Measure with IF statement and filter context

[robertoz]

Dear all,

I am curious, wheter this is possible.

Within calculated measure I am trying to calculate alternative results depending on the dimension. Let me explain on an demo/example:

I have got Table:

Item	Amount	Month
EBIT	1	Jan
EBIT	2	Feb
EBIT	1	Mar
Revenue	20	Jan
Revenue	20	Feb
Revenue	40	Mar
EBIT%	0,05	Jan
EBIT%	0,1	Feb
EBIT%	0,25	Mar

Sum Amount = SUM('Table'[Amount]) returns sums as expected. However for EBIT% I can NOT use Sum.  I do need to substitute Sum with (Sum of EBIT / Sum of Revenue). So I tried:

Measure1 = SUMX('Table'; IF('Table'[Item] <> "EBIT%"; CALCULATE(SUM('Table'[Amount])); CALCULATE(SUM('Table'[Amount]); 'Table'[Item] = "EBIT") / CALCULATE(SUM('Table'[Amount]); 'Table'[Item] = "Revenue")))

BUT it returns null for EBIT%:

 

It looks like Filters in CALCULATE do not work as expected or I do have some mistake.

Any Ideas?

Rem.: I know, I can create separate measure for EBIT%, however I do need to put all 3 Measures (Revenue / EBIT / EBIT% into one colum, like displayed above.

Thanks lot for your support.

[Omega · Accepted solution]

Try using the below measure: 

Measure = SWITCH(TRUE(),
        MAX(Table1[Item]) = "EBIT", SUM(Table1[Amount]),
        MAX(Table1[Item])= "EBIT%",CALCULATE(SUM(Table1[Amount]),Table1[Item] = "EBIT")/CALCULATE(SUM(Table1[Amount]),Table1[Item]="Revenue"),
        CALCULATE(SUM(Table1[Amount]),Table1[Item]="Revenue"))

[Omega · Accepted solution]

Try using the below measure: 

Measure = SWITCH(TRUE(),
        MAX(Table1[Item]) = "EBIT", SUM(Table1[Amount]),
        MAX(Table1[Item])= "EBIT%",CALCULATE(SUM(Table1[Amount]),Table1[Item] = "EBIT")/CALCULATE(SUM(Table1[Amount]),Table1[Item]="Revenue"),
        CALCULATE(SUM(Table1[Amount]),Table1[Item]="Revenue"))

[robertoz]

@Omega

G8! many thanks.

So the "magic" is, if I need to refer to dimension within IF/SWICH condition, I need to pack the dimension into function.

Btw. also this works:

Measure 3 = SWITCH(TRUE();
        MAX('Table'[Item])= "EBIT%"; CALCULATE(SUM('Table'[Amount]); 'Table'[Item] = "EBIT")/CALCULATE(SUM('Table'[Amount]); 'Table'[Item]="Revenue"); 
        CALCULATE(SUM('Table'[Amount])))

 and wit IF as well:

MeasureIF = IF(MAX('Table'[Item])= "EBIT%";
	CALCULATE(SUM('Table'[Amount]); 'Table'[Item] = "EBIT")/CALCULATE(SUM('Table'[Amount]);'Table'[Item]="Revenue"); 
        CALCULATE(SUM('Table'[Amount])))

Thanks a lot.

R.

[Omega]

You are right! I thought you need to use the amount of Revenue when you don't select EBIT or EBIT% and that's why I used Switch ()

[robertoz]

yes. thanks.

One related question came to my mind: now I have results in one table:

Item         Measure

EBIT            4

Revenue   80

EBIT %     0.4

Is there a trick, to get %-sign for EBIT %?

Means:

Item        Measure

EBIT            4

Revenue   80

EBIT %     0.4%

Thx.

[Omega · Accepted solution]

HYG. I calculated EBIT% as a variable and used Format function to change the format 🙂 

Measure = 
var EB = CALCULATE(SUM(Table1[Amount]),Table1[Item] = "EBIT")/CALCULATE(SUM(Table1[Amount]),Table1[Item]="Revenue")
return
SWITCH(TRUE(),
        MAX(Table1[Item]) = "EBIT", SUM(Table1[Amount]),
        MAX(Table1[Item])= "EBIT%",FORMAT(EB,"Percent"),
        CALCULATE(SUM(Table1[Amount]),Table1[Item]="Revenue"))

[robertoz]

Works lika a charm.

Many thanks for your support.

R.