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

[aditya_fractal]

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!

[Vannikannan04]

Hi @aditya_fractal.,

Did you find solution for this?

Thanks

[aditya_fractal]

Unfortunately, no couldn't find solution for this.

[v-eqin-msft]

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.

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

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

[aditya_fractal]

@v-eqin-msft  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?

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!