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.
Hello all,
I need to find a DAX formula to calculate some special indicators among the general ones which formula is "Sum".
Example :
Company USA has 2 plants: plant 1 & plant 2, each plant has its own KPI indicators. To get the KPI indicators of Company USA, formulas are used for the calculation.
* For indicators 1 & 5, the formula is simple, it is the "Sum" of both plants for correspondant date.
* For indicator 4, the formula is the "Average" value of both plants for the same date.
* For indicator 2 & 3, the formula is special, " Indicator 2 of Company USA = (indicator 2 of plant 1 x indicator 5 of plant 1 + indicator 2 of plant 2 x indicator 5 of plant 2) / indicator 5 of Company USA. The same formula for indicator 3.
The formulas are easy in Excel, but how to apply them in DAX in PBI report? I tried the DAX measure below by adding a table for "indicator 4" to calculate the "Average" Value, then the rest is "Sum". It works, but how to add other formula for the specil calculation of "indicator 2 & 3"?
Measure DAX = IF(CALCULATE(COUNTROWS('KPI AverageValue'), FILTER('KPI AverageValue', 'KPI AverageValue'[KPI AverageData]=SELECTEDVALUE(KPI_Global_Data[Data]))) =1 , average(KPI_Global_Data[Value]), SUM(KPI_Global_Data[Value]))
Here below please find images for explanations.
It would be great if you may help to find the solution.
Thanks in advance.
Best regards
Rachel
Solved! Go to Solution.
Hi @EZV12l,
In fact, power bi not suitable to handle these scenarios, data model tables not include column and row index.
You need to add index files to your table to filter the specific records calculations, then use a switch function to handle multiple calculations.
Write Conditional Statement Using SWITCH in DAX and Power BI - RADACAD
Regards,
Xiaoxin Sheng
Hi @EZV12l,
In fact, power bi not suitable to handle these scenarios, data model tables not include column and row index.
You need to add index files to your table to filter the specific records calculations, then use a switch function to handle multiple calculations.
Write Conditional Statement Using SWITCH in DAX and Power BI - RADACAD
Regards,
Xiaoxin Sheng
Use a SWITCH(TRUE(),...) statement to handle each of your scenarios separately.
Hello @lbendlin
Thank you for your reply, however, as I'm new to PBI, may you please explain more in details? Is it possible for you to show me an example of your formula SWITCH(TRUE(),...) for my problem above?
Many thanks.
Rachel
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |