Forum Discussion

Nikkoloz's avatar
Nikkoloz
New Member
2 years ago
Solved

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

CategoryCategorySort
Jan-2022 (Peak)1
Latest2

 

BreakdownSort
Food and non-alcoholic beverages1
Housing and utilities2
Transport3
Other4
Healthcare5
Hotels and Restaurants6

 

Jan-2022 (Peak)Food and non-alcoholic beveragesHousing and utilitiesTransportOtherHealthcareHotels and RestaurantsLatest
2.781.731.200.690.450.250.140.14
2.781.731.200.690.450.250.140.14
2.781.731.200.690.450.250.140.14
2.781.731.200.690.450.250.140.14
2.781.731.200.690.450.250.140.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

  • isjoycewang's avatar
    isjoycewang
    Solution 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 

    • Nikkoloz's avatar
      Nikkoloz
      New 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