Forum Discussion
Waterfall total different from calculated measure
Community ChampionIn the table, Account 5, 10, 12 are treated as subtotals but the waterfall chart (which charts +ve and -ve increments) uses each of those values as a +ve increment. At the end, these +ve increments get included in the total.
The total in the table is the measure you've written applied across the whole table.
The total in the chart is the net value of increments and decrements.
Thanks HotChilli
So I can't show the same total as in the table?
I tried another way but still not the expected result:
I made a support table as Category with start (Net Revenues) and end of the graphic (Free Cash Flow), created a Breakdown by group but then the only available classification is by amount.
I need to be able to order the amount respecting the Financial Statement, from account 1 to 14.
Can you please suggest a workaround? I don't see how to trick the graphic to show the value I want in the order I want.
- HotChilli
Isn't the data in the 'data' table what you want to display? It should work because it's all at the same granularity.
If you have difficulties with ordering, (it might work if it's in alphabetical order) then use 'Sort by Column' functionality.
If you have the relationships set up correctly, you should be able to sort by FormatCode column
Hi HotChilli thanks but I think I was not clear.
What I meant is that the results won't be organized in the correct order but instead, from low to high based on the amount, whether I select Category or Y Axis as sort by.
The category is ordered by the ID, so it should respect the ID and not the + or - amount.
Sort by Account (Category)Sort by "Waterfall" Measure (Y Axis)
The support table has 2 values support table - without relationships
The new formula is
Waterfall =
SWITCH(TRUE();
SELECTEDVALUE(AxisSupport[Account])="account1";[account1];
SELECTEDVALUE(AxisSupport[Account])="account14";[Consolidated Actual])
- HotChilli
What I'm referring to is the https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column#sort-using-the-sort-by-column-button feature. You want to sort by Account but you have to tell powerbi how you want it sorted.
Your data model could probably be simplified since all tables look practically the same (unless that's just test data and your real data requires the model)