cancel
Showing results for
Did you mean:  Helper III

## Help with formula DAX

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.

Best regards

Rachel

1 ACCEPTED SOLUTION  Community Support

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.

4 REPLIES 4  Community Support

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.  Helper III

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  Community Champion

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  Helper III

Hello @selimovd Denis,

Best regards

Rachel Announcements #### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022. #### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps Top Solution Authors
Top Kudoed Authors
Users online (3,162)