Forum Discussion
Exclude specific values from top n
Hi all,
I want to calculate the sales of top 5 companies as % of total, alll things worked well, but I found I have name "Others" as a company name. I want to exclude it from the top 5 companies, so the top 5 would only count those with meaningful names, such as Apple or Google. But I also want to keep the data of "Others" in the total number, so I can calculate the concentration ratio of companies.
In this case, simply putting a filter to exclude "Others" is not working.
Here is my current calculation:
Could anyone help? Thanks a lot!
hill , Try to create a measure like this and rank/TOPN on that
new sales =calculate([Sales], filter(data, data[companyname] <> "Others"))
Please try this expression instead.
Top 5 Concentration =
DIVIDE (
CALCULATE (
[Sales],
TOPN (
5,
FILTER (
VALUES ( data[companyname] ),
data[companyname] <> "Others"
),
[Sales], DESC
)
),
CALCULATE (
[Sales],
ALLSELECTED ( data[companyname] )
)
)Regards,
Pat
6 Replies
Please try this expression instead.
Top 5 Concentration =
DIVIDE (
CALCULATE (
[Sales],
TOPN (
5,
FILTER (
VALUES ( data[companyname] ),
data[companyname] <> "Others"
),
[Sales], DESC
)
),
CALCULATE (
[Sales],
ALLSELECTED ( data[companyname] )
)
)Regards,
Pat
Thank you! that is really great
hill , refer for TOPN with Others
https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/
Refer TOPN : https://www.youtube.com/watch?v=QIVEFp-QiOk
Thank you for your reply, but I am afraid that you are not answering my question.
"Others" is a company name, not a remaining part of top 5. Instead, it's part of top 5
"Others" is the aggregation of all small companies. So it can be very large to be top 1 in the category
hill , Try to create a measure like this and rank/TOPN on that
new sales =calculate([Sales], filter(data, data[companyname] <> "Others"))
