Forum Discussion
Breakdown category missing from waterfall chart measure
Hi,
I'm trying to create a waterfall chart with multiple columns. I have searched the forum and found a workaround using a measure with the switch function (https://community.fabric.microsoft.com/t5/Desktop/How-to-build-a-Waterfall-Chart-with-multiple-columns/td-p/1456529). I have tweaked the code and, for the first chart, it worked just fine, but for the second chart one of the breakdown categories is missing and I'm not sure why.
This is my data (tables are pasted from Excel and look distorted but a quick search tells me this is not an issue):
| Category | CategorySort |
| Jan-2022 (Peak) | 1 |
| Latest | 2 |
| Breakdown | Sort |
| Food and non-alcoholic beverages | 1 |
| Housing and utilities | 2 |
| Transport | 3 |
| Other | 4 |
| Healthcare | 5 |
| Hotels and Restaurants | 6 |
| Jan-2022 (Peak) | Food and non-alcoholic beverages | Housing and utilities | Transport | Other | Healthcare | Hotels and Restaurants | Latest |
| 2.78 | 1.73 | 1.20 | 0.69 | 0.45 | 0.25 | 0.14 | 0.14 |
| 2.78 | 1.73 | 1.20 | 0.69 | 0.45 | 0.25 | 0.14 | 0.14 |
| 2.78 | 1.73 | 1.20 | 0.69 | 0.45 | 0.25 | 0.14 | 0.14 |
| 2.78 | 1.73 | 1.20 | 0.69 | 0.45 | 0.25 | 0.14 | 0.14 |
| 2.78 | 1.73 | 1.20 | 0.69 | 0.45 | 0.25 | 0.14 | 0.14 |
And this is the code:
Contribution2 =
VAR _Peak = sum('Data Peak'[Jan-2022 (Peak)])
VAR _Food = sum('Data Peak'[Food and non-alcoholic beverages])
VAR _Housing = sum('Data Peak'[Housing and utilities])
VAR _Transport = sum('Data Peak'[Transport])
VAR _Other = sum('Data Peak'[Other])
VAR _Healthcare = sum('Data Peak'[Healthcare])
VAR _Hotels = sum('Data Peak'[Hotels and Restaurants])
VAR _Latest = sum('Data Peak'[Latest])
Return
SWITCH (
SELECTEDVALUE ('Category Peak'[CategorySort]),
1, SWITCH (
SELECTEDVALUE ('Breakdown Peak'[Breakdown] ),
"Food and non-alcoholic beverages", -1*(_Food-_Peak),
"Housing and utilities",-1*(_Housing-_Food),
"Transport", -1*(_Transport-_Housing),
"Other", -1*(_Other-_Transport),
"Healthcare", -1*(_Healthcare-_Other),
"Hotels and Restaurants", -1*(_Hotels-_Healthcare),
_Peak
),
2, SWITCH (
SELECTEDVALUE ('Breakdown Peak'[Breakdown]),
"Food and non-alcoholic beverages", 0,
"Housing and utilities",0,
"Transport",0,
"Other",0,
"Healthcare",0,
"Hotels and Restaurants",0,
_Latest
)
)
The result is the following chart:
As you can see, there are two "other" columns, whereas the first yellow column should be named "Hotels and Restaurants". I don't have any prior experience working with this, so I might be missing something very simple.
Thanks for any comments/suggestions!
Hi Nikkoloz,
Power BI automatically detects the Top 5 biggest breakdown and it's changeable.
You can change the settings in your visual so it won't show the "Other" group. Thanks.
Best Regards,
Joyce
2 Replies
- isjoycewangSolution Supplier
Hi Nikkoloz,
Power BI automatically detects the Top 5 biggest breakdown and it's changeable.
You can change the settings in your visual so it won't show the "Other" group. Thanks.
Best Regards,
Joyce
- NikkolozNew Member
Hi isjoycewang ,
Joyce, thanks! Coincidentally, the previous chart I created had the "other" group as the last category so evidently Power BI's default 6th group name was the same as my group name and thus the issue didn't stand out, particularly since the colours are also the same in my theme.
Thanks,
Nikoloz