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

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.