Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
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.
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.
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.
😞
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
140 | |
105 | |
102 | |
81 | |
67 |