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

       

       

      • 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.