cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aditya_fractal
Advocate I
Advocate I

Show only top 5 on Waterfall chart & group rest of the companies as Others

Hi everyone,

 

Hope you're all well!

 

I need very urgent help on one of the requirements. I'm stuck on this since quite long now. 

 

I want to show a waterfall chart where only TOP 5 companies growth is shown & rest of the companies are capped as OTHERS. 

 

Please allow me to explain in detail as below.

  1. Assuming that there are a total 100 companies in my dataset
  2. Waterfall chart should show the sales growth of the top 5 companies & rest as Other
  3. Couple of slicers to select Country, Categories, etc.

Now, Scenario I is that for some selection of the slicers I see top 5 companies + OTHERS in the waterfall chart as mentioned below:

  1. Company A
  2. Company B
  3. Company E
  4. Company C
  5. Company D
  6. Other

which is acceptable.

 

But for Scenario II, for some other selection of the slicers, I see the below companies in the waterfall chart. 

  1. Company A
  2. Company B
  3. Other
  4. Company C
  5. Company D
  6. Other

I very well understand the reason why I can see two OTHER(s) in the waterfall chart. Its becuase my dataset contains "OTHER" as one of the companies & its coming in the top 5 in terms of sales.

 

The requirement here is to club the two OTHER(s) into a single Other & see only the Top 5 which should be dynamic as we also have slicers.

 

Any help would be much much appreciated.

 

Thanks in advance!

2 REPLIES 2
Eyelyn9
Community Support
Community Support

Hi @aditya_fractal ,

 

According to my understand, you want to show top5 based on slicer and  remove the additional "Other" values in Waterfall visual ,right?

 

You could use the following formula to rank by each catetory(breakdown in waterfall visual):

rank = RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Coun] = MAX ( 'Table'[Coun]) ),
    CALCULATE ( MAX ( ( 'Table'[Value]) ) ),
    ,
    DESC
)

Then apply it to the filter pane.

12.1.1.1.PNG

 

From my research ,We need to set the MAXIMUM breakdown so that values will not be replaced by  *Other*  :

12.1.1.2.gif

 

Here is the pbix file.

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,
Eyelyn Qin

@Eyelyn9  Many thanks for your reply. I would like to point out a few things as below.

  • We do need to show 'Others' only one time in the water fall chart.
    • In my case, what's happening is two 'Others' are being shown. The reason for the same is as below.
      • The 1st 'Others' comes from the data itself where we've 'Others' as a company with quite high sale that it falls in the top 5
      • The 2nd 'Others' is created by Power BI waterfall chart as we're limiting the breakdown to 5.

From what I understood from your answer is that if we remove the limit (5) from the breakdown, 'Others' which is created automatically by Waterfall chart would be removed but instead we want to combine the two 'Others' into a single company somehow.

Additionally, even if we go by applying filter on the visual using RANK measure, it would result in showing the full year sales only for the TOP 5, right?

aditya_fractal_0-1606887720436.png

To get into more easier understanding,

  • Other marked in RED is the one which exists in the data
  • Other marked in YELLOW is the one which POWER BI Waterfall chart has created by combining all the other companies
  • We need to combine these two 'OTHERS' into a single one
  • Also, if we apply rank filter on this visual i.e. TOP 5, in that case I think the FY2018 & FY2019 will also show the sales for only the TOP 5 companies, right?

Thanks once again!

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors