DAX formula for special values among values with "Sum" formula

[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

[Anonymous · Accepted 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

[lbendlin]

Use a SWITCH(TRUE(),...) statement to handle each of your scenarios separately.

[EZV12]

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