Waterfall total different from calculated measure

[Anonymous]

Dear,

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.

Waterfall & Table

I have the following model:

RelationsAccount PlanCFFormatData

I have two formulas:

actual = sum(Data[Amount])

Consolidated Actual =

VAR subtotalactual = IF(COUNTROWS(CFFormat)=1;

    CALCULATE([actual];ALL(CFFormat);CFFormat[FormatCode]<=VALUES(CFFormat[FormatCode])))

VAR iffiltered = ISFILTERED(AccountPlan[Classification2])

VAR subtotal = MAX(CFFormat[Subtotal])

RETURN

IF(AND(iffiltered=TRUE();subtotal=1);BLANK();

SWITCH(

    TRUE();

    SELECTEDVALUE(CFFormat[Subtotal])=0;[actual];

    SELECTEDVALUE(CFFormat[Subtotal])=1;subtotalactual;BLANK()

)

)

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.

Could you please advise? 

[HotChilli]

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]

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

[Anonymous]

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-butto... 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)

[Anonymous]

Hi @HotChilli , in both examples the data is sorted by the feature you suggested.

The problem is none of the solutions I could come up with show me what I need.

In the first example I had the problem that the total is not what I want:

And the second example, in which I did a support table for the axis, PBI don't respect the sort by column feature. It only sort by amounts and not account order.