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
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

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