Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi,
Can you post your dummy workbook and the required output. I can give it a try.
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
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
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.
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
Hi,
I do not see 16494443 anywhere in your PBI file.
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.
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |