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.
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 @EZV12 ,
Here are the steps you can follow:
1. Create a calculated table.
Table 2 =
UNION('Plant1','Plant2')
2. Create measure.
Measure_2021/01 =
SWITCH(
TRUE(),
MAX('Table 2'[KPI])="Indicator1"||MAX('Table 2'[KPI])="Indicator5",SUM('Table 2'[2021/01]),
MAX('Table 2'[KPI])="Indicator4",AVERAGE('Table 2'[2021/01]),
MAX('Table 2'[KPI])="Indicator2",
FORMAT((MINX(FILTER(ALL('Table 2'),[Index]=2),[2021/01])*MINX(FILTER(ALL('Table 2'),[Index]=5),[2021/01])+
MAXX(FILTER(ALL('Table 2'),[Index]=2),[2021/01])*MAXX(FILTER(ALL('Table 2'),[Index]=5),[2021/01]))
/
SUMX(FILTER(ALL('Table 2'),[KPI]="Indicator5"),'Table 2'[2021/01]),"Percent"),
MAX('Table 2'[KPI])="Indicator3",
FORMAT((MINX(FILTER(ALL('Table 2'),[Index]=3),[2021/01])*MINX(FILTER(ALL('Table 2'),[Index]=5),[2021/01])+
MAXX(FILTER(ALL('Table 2'),[Index]=3),[2021/01])*MAXX(FILTER(ALL('Table 2'),[Index]=5),[2021/01]))
/
SUMX(FILTER(ALL('Table 2'),[KPI]="Indicator5"),'Table 2'[2021/01]),"Percent")
)
Measure_2021/02 =
SWITCH(
TRUE(),
MAX('Table 2'[KPI])="Indicator1"||MAX('Table 2'[KPI])="Indicator5",SUM('Table 2'[2021/02]),
MAX('Table 2'[KPI])="Indicator4",AVERAGE('Table 2'[2021/02]),
MAX('Table 2'[KPI])="Indicator2",
FORMAT((MINX(FILTER(ALL('Table 2'),[Index]=2),[2021/02])*MINX(FILTER(ALL('Table 2'),[Index]=5),[2021/02])+
MAXX(FILTER(ALL('Table 2'),[Index]=2),[2021/02])*MAXX(FILTER(ALL('Table 2'),[Index]=5),[2021/02]))
/
SUMX(FILTER(ALL('Table 2'),[KPI]="Indicator5"),'Table 2'[2021/02]),"Percent"),
MAX('Table 2'[KPI])="Indicator3",
FORMAT((MINX(FILTER(ALL('Table 2'),[Index]=3),[2021/02])*MINX(FILTER(ALL('Table 2'),[Index]=5),[2021/02])+
MAXX(FILTER(ALL('Table 2'),[Index]=3),[2021/02])*MAXX(FILTER(ALL('Table 2'),[Index]=5),[2021/02]))
/
SUMX(FILTER(ALL('Table 2'),[KPI]="Indicator5"),'Table 2'[2021/02]),"Percent")
)
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @EZV12 ,
Here are the steps you can follow:
1. Create a calculated table.
Table 2 =
UNION('Plant1','Plant2')
2. Create measure.
Measure_2021/01 =
SWITCH(
TRUE(),
MAX('Table 2'[KPI])="Indicator1"||MAX('Table 2'[KPI])="Indicator5",SUM('Table 2'[2021/01]),
MAX('Table 2'[KPI])="Indicator4",AVERAGE('Table 2'[2021/01]),
MAX('Table 2'[KPI])="Indicator2",
FORMAT((MINX(FILTER(ALL('Table 2'),[Index]=2),[2021/01])*MINX(FILTER(ALL('Table 2'),[Index]=5),[2021/01])+
MAXX(FILTER(ALL('Table 2'),[Index]=2),[2021/01])*MAXX(FILTER(ALL('Table 2'),[Index]=5),[2021/01]))
/
SUMX(FILTER(ALL('Table 2'),[KPI]="Indicator5"),'Table 2'[2021/01]),"Percent"),
MAX('Table 2'[KPI])="Indicator3",
FORMAT((MINX(FILTER(ALL('Table 2'),[Index]=3),[2021/01])*MINX(FILTER(ALL('Table 2'),[Index]=5),[2021/01])+
MAXX(FILTER(ALL('Table 2'),[Index]=3),[2021/01])*MAXX(FILTER(ALL('Table 2'),[Index]=5),[2021/01]))
/
SUMX(FILTER(ALL('Table 2'),[KPI]="Indicator5"),'Table 2'[2021/01]),"Percent")
)
Measure_2021/02 =
SWITCH(
TRUE(),
MAX('Table 2'[KPI])="Indicator1"||MAX('Table 2'[KPI])="Indicator5",SUM('Table 2'[2021/02]),
MAX('Table 2'[KPI])="Indicator4",AVERAGE('Table 2'[2021/02]),
MAX('Table 2'[KPI])="Indicator2",
FORMAT((MINX(FILTER(ALL('Table 2'),[Index]=2),[2021/02])*MINX(FILTER(ALL('Table 2'),[Index]=5),[2021/02])+
MAXX(FILTER(ALL('Table 2'),[Index]=2),[2021/02])*MAXX(FILTER(ALL('Table 2'),[Index]=5),[2021/02]))
/
SUMX(FILTER(ALL('Table 2'),[KPI]="Indicator5"),'Table 2'[2021/02]),"Percent"),
MAX('Table 2'[KPI])="Indicator3",
FORMAT((MINX(FILTER(ALL('Table 2'),[Index]=3),[2021/02])*MINX(FILTER(ALL('Table 2'),[Index]=5),[2021/02])+
MAXX(FILTER(ALL('Table 2'),[Index]=3),[2021/02])*MAXX(FILTER(ALL('Table 2'),[Index]=5),[2021/02]))
/
SUMX(FILTER(ALL('Table 2'),[KPI]="Indicator5"),'Table 2'[2021/02]),"Percent")
)
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear @v-yangliu-msft ,
Many thanks for your help! It works.
By the way, I wonder if there is a possibility to create a general formula for all dates instead of 1 formula for each month as the calculation is the same each time.
Best regards
Rachel
Hey @EZV12 ,
I don't really understand the case.
Can you show the tables in Power BI and tell me how the result should look like?
Thank you and best regards
Denis
Hello @selimovd Denis,
Sorry for the late reply. Please find here the link to the file pbix : https://1drv.ms/u/s!AnbNXrfr-X8jd2mk6szlKCvzUZQ?e=A4ucp7
Thank you for your help.
Best regards
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |