DAX calculation based on field constraint

[Anonymous]

I am trying to create a card visual which contains a dynamic calculation - in order to have the results filterable with slicers. 

Within the calculation however, there are certain contraints that needs to be applied and I am unsure how to write this in DAX. Based on the data screenshot below i basically need the calculation to do the following:

Result = Value (Case = Base & Scenario = 1. None) - Value (Case = Sale & Scenario = 2. Combined)

Then I need to use the result to calculate a % change:

% Change = Result /  Value (Case = Base & Scenario = 1. None)

Any advice on the DAX formula for this? I have tried to use Calculate combined with Filter, however not been able to get it to work.

[MFelix]

Hi ,

Not on my computer now but your measure should be something like this

Result =
CALCULATE (
SUM ( Table[VALUE] );
Table[Case] = "Base"
&& Table[Scenario] = "1. None"
)
- CALCULATE (
SUM ( Table[VALUE] );
Table[Case] = "Sale"
&& Table[Scenario] = "2. Combined"
)

The other measure
% Change = [Result] /
CALCULATE (
SUM ( Table[VALUE] );
Table[Case] = "Base"
&& Table[Scenario] = "1. None"
)


Should work.

Regards
MFelix

[Anonymous]

@MFelix  Thanks - I have used the formula structure as suggested, but I get the following error message:

"The expressions contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression"

[MFelix · Accepted solution]

Hi @Anonymous,

Try the following changes:

Result =
CALCULATE (
SUM ( Table[VALUE] );
Table[Case] = "Base";
Table[Scenario] = "1. None"
)
- CALCULATE (
SUM ( Table[VALUE] );
Table[Case] = "Sale";
Table[Scenario] = "2. Combined"
)

The other measure
% Change = [Result] /
CALCULATE (
SUM ( Table[VALUE] );
Table[Case] = "Base";
Table[Scenario] = "1. None"
)


Regards
MFelix

[Anonymous]

@MFelix  Thank you very much - that seems to have done the trick!