cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EZV12
Helper III
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.

 

EZV12_0-1622134369123.pngEZV12_1-1622134395978.pngEZV12_2-1622134409947.pngEZV12_3-1622134431039.pngEZV12_4-1622134456159.png

 

It would be great if you may help to find the solution.

Thanks in advance.

Best regards

Rachel

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
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.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
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.

View solution in original post

lbendlin
Super User III
Super User III

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

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors