GROUP BY Based on Ranking

[Anonymous]

Hi Everyone,

I have this table (customer name blanked out due to sensitivity):

I want to create a pie chart showing me the top 5 customers and then the remaining values in All Other based on $ volume. I can't seem to find a way to properly group them and show their values

IE. Group BY Rank is something i tried but failed. Value should match the value column.

Any ideas on how I can better go about this? 

Thanks!

Anthony

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file, and I hope it helps to get an idea to solve yours.

Rank N values and others: =
VAR topNselect =
    SELECTEDVALUE ( 'Rank N'[Rank N] )
VAR topNvalueTotal =
    CALCULATE (
        SUM ( Data[Value] ),
        TOPN ( topNselect, ALL ( Customers ), CALCULATE ( SUM ( Data[Value] ) ), DESC )
    )
VAR totalvalue =
    CALCULATE ( SUM ( Data[Value] ), REMOVEFILTERS ( Customers ) )
RETURN
    IF (
        HASONEVALUE ( Customers[Customer] ),
        IF (
            SELECTEDVALUE ( Customers[Customer] ) = "Others",
            totalvalue - topNvalueTotal,
            CALCULATE (
                SUM ( Data[Value] ),
                KEEPFILTERS (
                    TOPN ( topNselect, ALL ( Customers ), CALCULATE ( SUM ( Data[Value] ) ), DESC )
                )
            )
        ),
        totalvalue
    )