Exclude specific values from top n

[hill]

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:

Top 5 Concentration = DIVIDE(CALCULATE([Sales],topn(5,GROUPBY(data,data[companyname]),[Sales])),CALCULATE([Sales],ALLSELECTED(data[companyname])))

Could anyone help? Thanks a lot!

[amitchandak · Accepted solution]

@hill , Try to create a measure like this and rank/TOPN on that

new sales =calculate([Sales], filter(data, data[companyname] <> "Others"))

[mahoneypat · Accepted solution]

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

[hill]

Thank you! that is really great

[amitchandak]

@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

[hill]

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

[amitchandak · Accepted solution]

@hill , Try to create a measure like this and rank/TOPN on that

new sales =calculate([Sales], filter(data, data[companyname] <> "Others"))

[hill]

Thank you very much! it works for me