Forum Discussion

robertoz's avatar
robertoz
Advocate I
7 years ago
Solved

Measure with IF statement and filter context

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:

 

ItemAmountMonth
EBIT1Jan
EBIT2Feb
EBIT1Mar
Revenue20Jan
Revenue20Feb
Revenue40Mar
EBIT%0,05Jan
EBIT%0,1Feb
EBIT%0,25Mar

 

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.

Need Help
  • Omega's avatar
    Omega
    7 years ago

    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's avatar
    Omega
    7 years ago

    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"))

6 Replies

  • Omega's avatar
    Omega
    Impactful Individual
    7 years ago

    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's avatar
      robertoz
      Advocate I
      7 years ago

      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's avatar
        Omega
        Impactful Individual
        7 years ago

        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 ()