Help with formula DAX

[EZV12]

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

@selimovd  @v-yiruan-msft 

[EZV12]

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

@selimovd  @v-yiruan-msft 

[v-yangliu-msft · Accepted 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.

[EZV12]

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

[selimovd]

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

[EZV12]

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