Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
zervino
Helper I
Helper I

Waterfall chart with specific breakdown order

I want to create a waterfall chart with this structure:

 

zervino_0-1713210682464.png

 

 

YearTeamVolume

2022

A50
2022B30
2022C20
2023A80
2023B15
2023C30

 

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?

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

vtangjiemsft_0-1713335401884.png

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.

vtangjiemsft_1-1713335494677.png

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. 

 

View solution in original post

5 REPLIES 5
v-tangjie-msft
Community Support
Community Support

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 }
    }
) 

 

vtangjiemsft_0-1713248185780.png

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

vtangjiemsft_1-1713248259370.png

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

vtangjiemsft_2-1713248355003.png

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. 

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.

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.

vtangjiemsft_0-1713335401884.png

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.

vtangjiemsft_1-1713335494677.png

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
Resolver III
Resolver III

Hi @zervino,

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

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

 

😞

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.