cancel
Showing results for
Did you mean:
Highlighted
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
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.

Regular Visitor

## Re: summary measure calc ignoring prior IF statement result

Oh Great!

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

Thanks much!

Robert

Frequent Visitor

## Re: summary measure calc ignoring prior IF statement result

Hi,

Please check if this is what you are expecting.

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

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.

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

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

## 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.

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:

"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

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

## Re: summary measure calc ignoring prior IF statement result

Hi,

I do not see 16494443 anywhere in your PBI file.

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.