Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

GROUP BY Based on Ranking

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's avatar
    Jihwan_Kim
    4 years ago

    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
    )

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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
    )