Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

summary measure calc ignoring prior IF statement result

Hello DAX users: 

 

I am calculating a summary measure based on this measure: 

Cumulative Total MAX = IF ([Cumulative Total] >= [Total Cost Measure], [Total Cost Measure], [Cumulative Total])

The result is working well.  Now, I need to use that measure to calculate a summary measure using the following code: 

Incremental Cost by Year = 
CALCULATE( 
        [Cumulative Total MAX], 
        FILTER( 
            ALLSELECTED(MASTER),
            MAX(MASTER[Year]) >= MASTER[Year]
        )
    ,
        ALLSELECTED('Vulnerable Groups')
)

This code works well, except it is ignoring the result from my previous IF statement.  Somehow I need to preserve the filter context above, but also keep the measure resulting from the IF statement.  I have attempted myriad variations of the second calc, but to no avail.  Thanks much for any thoughts on solving this.  


Robert 

12 REPLIES 12
SaumyaShetty
Frequent Visitor

Hi,

 

Can you post your dummy workbook and the required output. I can give it a try.

Anonymous
Not applicable

Oh Great!

 

Please find the file here.

 

Also, please note that the page includes a hierarchy slicer. 

 

Thanks much!


Robert 

Hi,

 

Please check if this is what you are expecting. 

 

If yes then please make  modification to your DAX for measure                                                                                                                      

Incremental Cost by Year =
CALCULATE(
        [Cumulative Total MAX] ,ALLSELECTED('Vulnerable Groups')
)

 

Final.PNG

Anonymous
Not applicable

Hello Saumya, 

 

Thanks for your quick response.  In fact,  I am looking for the values in the "Incremental Cost by Year" to equal the sum of the values for all the Vulnerable Groups for each year.   The issue with the original measure is that it is ignoring the prior results from the IF statement-  which is showing up in the last 2 years.  Here is the graphed result from my orginal measure with the total sum on top of each year's stacked total.   

 

Original result.PNG

 

And, here is the same result from the new measure you provided:

New result.PNG

New result.PNG

This result does not correct the underlying issue and results in more of the summed annual totals being different from the actual sum of the the stacked values compared to the original measure. 

 

The issue is that my calc is ignoring the result from the prior IF statement.  Do you have any ideas on how to ensure that the prior IF statement result  is incorporated into the sum measure while not changing the filter as it is currently set up?  Thanks much for your support.  -Robert 

 

Hi,

 

Please describe the question first rather than merely posting a visual or your DAX formula.  Share a dataset and show the result in a simple Table.  From the Table, we can create any visual we want.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hello Ashish,

 

My problem is that my summary measure "Incremental Cost by Year" is ignoring the IF statement from the previous measure upon which it calculated.   My summary measure based on this measure: 

Cumulative Total MAX = IF ([Cumulative Total] >= [Total Cost Measure], [Total Cost Measure], [Cumulative Total])

The result of that calc is working well.  Now, I need to use that adjusted measure to calculate a summary measure using the following code: 

Incremental Cost by Year = 
CALCULATE( 
        [Cumulative Total MAX], 
        FILTER( 
            ALLSELECTED(MASTER),
            MAX(MASTER[Year]) >= MASTER[Year]
        )
    ,
        ALLSELECTED('Vulnerable Groups')
)

This code works well, except it is ignoring the result from my previous IF statement.  Somehow I need to preserve the filter context above, but also keep the measure resulting from the IF statement.  

 

You can see the issue most clearly in the last few rows of the table below: 

New Table.PNG

"Incremental Cost by Year" for 2025 should be $12,000,000 + $1,580,150 + $2,381,603 = $15,961,753.  However, I am getting $16,494,443.   For some reason that measure is calculating based on the "Cumulative Total" measure ($12,000,000 + $2,112,840 + $2,381,603) instead of the adjusted measure "Cumulative Total MAX".  

 

My file is here.  I greatly appreciate any help you may be able to provide. 

 

Kind regards.  -Robert 

Hi,

 

I do not see 16494443 anywhere in your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Apologies.  I reselected the "vulnerable groups" and "years" on the slicer to match the screen shot from my previous post.  

 

Here is the file. Cost Model 

 

Thanks much. 

Hi,

 

I have tried but cannot solve it.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Ok.   Thanks for your efforts!  

 

I have literally spent over 100 hours now working on solving this.   I am really astounded that this software is so clunky that it can't provide the correct totals on a stacked bar chart...  

Same here tried multiple ways but could not come with your desired resultant set.

Anonymous
Not applicable

So, my question is:  How can I adjust the DAX formula on "Incremental Cost by Year" to ensure that it using the result from the IF statement specified in the "Cumulative Total MAX" measure?  Thanks! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.