cancel
Showing results for
Did you mean:
Helper III

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

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 @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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
3 REPLIES 3
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User III

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

Helper III

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

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.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors