Forum Discussion

hill's avatar
hill
Helper I
5 years ago
Solved

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...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

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

     

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

  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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's avatar
hill
Helper I
5 years ago

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's avatar
amitchandak
Super User
5 years ago

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

 

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

  • hill's avatar
    hill
    Helper I
    5 years ago

    Thank you very much! it works for me