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 Ta...
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"))
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 ()

 

 

  • robertoz's avatar
    robertoz
    Advocate I
    7 years ago

    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's avatar
      Omega
      Impactful Individual
      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"))
      • robertoz's avatar
        robertoz
        Advocate I
        7 years ago

        Works lika a charm.

        Many thanks for your support.

        R.