Is it possible to create a calculation in which part of the calulation isnt affected by an attribute

LukePa

I have number of cars sold and total number of cars sold as 2 measures and sales channel as an attribute

Number of cars sold/total number of cars sold as my calulation and sales channel as a dashboard filter, this will equal 1 when the dashboard filter inst being used.

Is it possible to have sales channel filter the 'number of cars sold' but not the 'total number of cars sold'

Gianni Ceresa

Hi,

Assuming your source is a subject area, you can either use a formula to keep the "total number of cars sold" at the grand total level of your sales channel dimension, or add a new measure in your logical table fixing the content level of that measure to always be on top of the sales channel dimension.

Chere-Oracle

Instead of applying Sales Channel filter to the whole query, only apply it within the column formula using the FILTER() function. To do this, you'll have to set a presentation variable name in the dashboard prompt; e.g., sales_channel.

FILTER( "Cars Sold" USING ( "Sales Channel" LIKE '@{sales_channel}{%}' ) ) / "Cars Sold"

LukePa

Hi @Chere-Oracle, what do you mean by presentation variable name in the dashboard promt?

Gianni Ceresa

@LukePa , what part of the product are you using? DV workbook? A "classic" analysis and dashboard? A Publisher report?

The answer could change based on that…

By "set a presentation variable name in the dashboard board", she means that because you can't filter the whole query by your "sales channel" (as that's the reason you are asking the question) you need to have access to the value selected by your users through a variable. Therefore you modify your prompt/drow-down list/dashboard filter to set a variable with the value of the selected sales channel.

Then you can reference that variable in the expression of the column for "Number of cars sold".

LukePa

Hi @Gianni Ceresa , this is for a DV workbook.

Chere-Oracle

Sorry, LukePa. Since you wrote "dashboard filter," I assumed you were using classic, which includes dashboard prompts.

In your case, it has to be a parameter, not a variable. The parameter name (e.g., sales_channel) can be inserted in the calculation like this:

FILTER( "Cars Sold" USING ( "Sales Channel" LIKE '@parameter("sales_channel")' ) ) / "Cars Sold"