Top N and Others

Power BI
1

Dear sir/ madam,
In Power BI, I am trying to create Top 3 Selection of defect types (in qty) and then combine the other defect types in “Others”.
I would also like to show the result in a column chart as shown in example below.
Please see file attached for the data.

image

Please advise. Thanks.

Sample Data for Top N & Others.xlsx (125.6 KB)

2

Hi @Khaiboon - Please check below links. There are similar ones available online.

Thanks
Ankit J

3

Hi Ankit,
Thanks so much for the info.
The Youtube video was great and I was able to duplicate the solution.

I have a question about the Total values.
Please see below snippets of the Total between the 2 tables. (from the Youtube video)
The Totals were not able to match and I think some of the value was not calculated in “Others” even though the Top N values were matching.

image
image699×469 70.2 KB

When I used my data, I have the same problem too.

Please advise,
Thanks so much

4

Hi @Khaiboon - For Calculating total correctly we need to add one more condition in return statement like.

if(HASONEVALUE('Table'[Product]),if(CurrProduct = "Others",OtherSales,TopNSales),[TotalQty])

Check attached PBIX.

Khaiboon.pbix (28.4 KB)

Thanks
Ankit J

1 Like
5

Hi Ankit,

Thanks so much! Works great!

1 Like