Forum Discussion
Waterfall "Total" column is incorrect
I have a developed a report relating to risk management that compares risks left over from 2023 and monitors progress as those risks are resolved. You can see that the gauge is showing the total percent accurately, which is simply dividing the count of backlog risks processed (1050) by the static total backlog count from end of last year (3451):
[BacklogProcessed] / [RisksDec31]
However, I then had the task of breaking out the progress by month as well, and I thought a waterfall chart would be good for this purpose. First, I made a measure to calculate the backlog risk count from the previous month:
PrevMonthRiskCount =
CALCULATE(DISTINCTCOUNT(risk_table[risk_id]), PARALLELPERIOD(calendar_table[date].[Date],-1,MONTH))
Then I plugged that calc into a monthly percent calc:
Backlog Processed % Monthly =
DIVIDE(
([PrevMonthRiskCount] - DISTINCTCOUNT(risk_table[risk_id])) --to find # of risks processed in the month
,
CALCULATE([RiskCountLatest], PARALLELPERIOD(calendar_table[date],-1,MONTH)) --to find starting risk count for each month
,0)
However, my DAX must be off somewhere because the Waterfall total (34.4%) always comes out higher than the gauge total (30.4%). Any idea where I went wrong?
Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
3 Replies
- Greg_Deckler
Community Champion
Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8Hi Greg_Deckler
Thanks very much for this insight. I had no idea Power BI struggled so much with something as simple as SUM on a table or chart. I upvoted that idea as requested. Your other resources will be very helpful in the future as well.
For the sake of this particular ask (and since the gauge total is already working), let's take the waterfall's "Total" column out of the equation and turn this into a standard bar chart:How can I get those monthly counts to show as a percent of 3,541 instead? So the columns would then read 13.5%, 7.6%, 6.8%, and 4.2%, respectively. Currently I am calculating these counts with a DAX measure as follows:
CALCULATE(DISTINCTCOUNT('risk_table'[risk_id]), PARALLELPERIOD(calendar_table[date_dt],-1,MONTH)) --Prev Month Risk Count - CALCULATE(DISTINCTCOUNT('risk_table'[risk_id]), risk_table[snapshot_dt] = MAX(risk_table[snapshot_dt])) --Current Risk Countwhere "snapshot_dt" is the latest snapshot of the data, current as of today.