Waterfall chart with specific breakdown order

[zervino]

I want to create a waterfall chart with this structure:

Year	Team	Volume
2022	A	50
2022	B	30
2022	C	20
2023	A	80
2023	B	15
2023	C	30

I want to show the break down in that specific order, Team A, B and C. Regardless of the volume value (and wether it is positive or negative).

Currently PowerBI will show the breakdown sorted by ascending/descending volume.

Is there a way to achieve this without 3rd party plugins?

[v-tangjie-msft]

Hi @zervino ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a table and then create a model relationship.

SampleData = DATATABLE ( 
    "Team", STRING, "Index", INTEGER,
    {
        { "A", 1 },
        { "B", 2 },
        { "C", 3 }
    }
) 

(3)Select the Team column of the SampleData table and sort by the Index column.

(4)Place the Team column of the SampleData table on the visual object.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[zervino]

Thanks for the answer.

The result is different from my initial chart. Your solution adds up the values for each team and shows a total for both years. There is no waterfall from 2022 to 2023.

[v-tangjie-msft · Accepted solution]

Hi @zervino ,

I apologize for misunderstanding your point. By design, it is not possible to sort by column when both the sort field and the breakdown field have values. Here is an alternative solution:

(1)We can create two tables.

Table 2 = UNION(VALUES('Table'[Team]),VALUES('Table'[Year]))

Table 3 = DATATABLE ( 
    "Team", STRING, "Index", INTEGER,
    {
        { "2022", 1 },
        { "A", 2 },
        { "B", 3 },
        { "C", 4 },
         { "2023", 5 }
    }
) 

We can create a column in Table2.

Volume = 
 var _a= CALCULATE(SUM('Table'[Volume]),FILTER('Table',[Year]=EARLIER('Table 2'[Team])))
 var _b=CALCULATE(SUM('Table'[Volume]),FILTER('Table',[Team]=EARLIER('Table 2'[Team]) && [Year]="2023"))
 var _c=CALCULATE(SUM('Table'[Volume]),FILTER('Table',[Team]=EARLIER('Table 2'[Team]) && [Year]="2022"))
 return IF(_a=BLANK(),_b-_c,_a)

We can create a model relationship.

Select the Team column of the [Table 3] table and sort by the Index column.

Place the Team column of the [Table 3] table on the visual object.

However, the color of the columns will not be consistent with your expected color, which is due to the design. If you would like to give feedback and suggestions on Power BI Desktop features, you can post ideas to the IDEA forum.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[wini_R]

Hi @zervino,

Possibly this approach could help: Power BI & DAX: How to Make Waterfall Charts Work

[zervino]

Thanks for the message, but that video doesn't cover how to sort the waterfall breakdown.

😞