Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"))
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"))
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"))
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.
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 ()
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.
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"))
Works lika a charm.
Many thanks for your support.
R.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |