Waterfall total different from calculated measure

Question

Dear,&nbsp;I'm having an agregattion problem in the total of the Waterfall graphic.In the table I have the correct amount: 1.200 but in the Waterfall it gives 5400.&nbsp;Waterfall &amp; Table&nbsp;I have the following model:RelationsAccount PlanCFFormatDataI have two formulas:actual = sum(Data[Amount])&nbsp;Consolidated Actual =VAR subtotalactual = IF(COUNTROWS(CFFormat)=1;&nbsp;&nbsp;&nbsp; CALCULATE([actual];ALL(CFFormat);CFFormat[FormatCode]&lt;=VALUES(CFFormat[FormatCode])))VAR iffiltered = ISFILTERED(AccountPlan[Classification2])VAR subtotal = MAX(CFFormat[Subtotal])RETURNIF(AND(iffiltered=TRUE();subtotal=1);BLANK();SWITCH(&nbsp;&nbsp;&nbsp; TRUE();&nbsp;&nbsp;&nbsp; SELECTEDVALUE(CFFormat[Subtotal])=0;[actual];&nbsp;&nbsp;&nbsp; SELECTEDVALUE(CFFormat[Subtotal])=1;subtotalactual;BLANK()))&nbsp;I don't understand what should be different to have the correct total amount in the waterfall since the table gives me the correct cumullative amount.&nbsp;Could you please advise?&nbsp;

hotchilli · Answer

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

anonymous · Answer

Thanks&nbsp;HotChilli&nbsp;&nbsp;So I can't show the same total as in the table?&nbsp;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&nbsp;order the amount respecting the Financial Statement, from account 1 to 14.&nbsp;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 · Answer

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

anonymous · Answer

Hi&nbsp;HotChilli&nbsp;thanks but I think I was not clear.&nbsp;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)&nbsp;The support table has 2 values&nbsp;support table - without relationshipsThe new formula is&nbsp;Waterfall =SWITCH(TRUE();SELECTEDVALUE(AxisSupport[Account])="account1";[account1];SELECTEDVALUE(AxisSupport[Account])="account14";[Consolidated Actual])

hotchilli · Answer

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.&nbsp; You want to sort by Account but you have to tell powerbi how you want it sorted.&nbsp;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)

Forum Discussion

Waterfall total different from calculated measure

6 Replies

Related content

Dynamic Calculation Different Period

Different Calculations

Waterfall with different measures and different colours

Waterfall Chart

Waterfall Chart Help