cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RKolesar Regular Visitor
Regular Visitor

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
Frequent Visitor

Re: summary measure calc ignoring prior IF statement result

Hi,

 

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

RKolesar Regular Visitor
Regular Visitor

Re: summary measure calc ignoring prior IF statement result

Oh Great!

 

Please find the file here.

 

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

 

Thanks much!


Robert 

SaumyaShetty Frequent Visitor
Frequent Visitor

Re: summary measure calc ignoring prior IF statement result

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

RKolesar Regular Visitor
Regular Visitor

Re: summary measure calc ignoring prior IF statement result

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 

 

Super User
Super User

Re: summary measure calc ignoring prior IF statement result

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.

RKolesar Regular Visitor
Regular Visitor

Re: summary measure calc ignoring prior IF statement result

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 

RKolesar Regular Visitor
Regular Visitor

Re: summary measure calc ignoring prior IF statement result

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! 

Super User
Super User

Re: summary measure calc ignoring prior IF statement result

Hi,

 

I do not see 16494443 anywhere in your PBI file.

RKolesar Regular Visitor
Regular Visitor

Re: summary measure calc ignoring prior IF statement result

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.