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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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 Kudoed Authors