Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Top 10 / Other

I have created a Power BI table which ranks the top 10 customers by sales.  What now I need to do is take all of the remaining customers and combine them into one customer named "Other" and post it a...
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    Anonymous

     

    In this scenario, I think you can firstly create a calculated column for RANK:

     

     

    RANK=
RANKX(ALL(Table), SUMX(Table, Table[Sales]))

    Then create a display name column based on this RANK column:

     

     

     

    DISPLAY_CUSTOMER=
IF(Table[Rank]>10,"Other",Table[Customer])

    Now you just need to drag the DISPLAY_CUSTOMER column into your table visual, all the "Other"s will be aggregated.

     

    Regards,

     

     

  • jahida's avatar
    jahida
    10 years ago

    v-sihou-msft I was thinking about that, the only reason that solution would not work is if the Top 10 needs to be dynamic ie. respond to filters. If filters don't really matter, then that solution is great.

  • OwenAuger's avatar
    OwenAuger
    10 years ago

    Here's a template for TopN & Other I've been playing with:

    https://www.dropbox.com/s/59cct4in6zqbxaj/Sales%20Top%20Other.pbix?dl=1

     

    The final measure is [Sales Amount Top & Other] which is displayed per Customer for Top Customers, otherwise just totalled.

    I also threw in a Rank measure.

     

    It might not fit everyone's requirements, but just another idea to throw into the mix ;)

     

Hspuybroek's avatar
Hspuybroek
Frequent Visitor
9 years ago
Hi
 
Is it possible to do this without the Except function? I am using a Tabular model and I can not choose the Except function. Only the AllExcept function. Your top 10 measure is not problem, but the Other category doens't work.
 

To be clear I want it to work dynamic like your example https://www.dropbox.com/s/59cct4in6zqbxaj/Sales%20Top%20Other.pbix?dl=1

Hoe can I use the allexcept function for this? Can’t get it to work.
I did this for example :
 
Uitval Top2 = IF([Customer Rank By Selections]<=[Selected Top NNumber];[Selected Top N Value];
IF(HASONEVALUE('ANG Fouten'[LPP_NAME]);
IF(VALUES('ANG Fouten'[LPP_NAME]) = "Overige";
SUMX(FILTER (ALL('ANG Fouten'[LPP_NAME]);[Customer Rank By Selections] >[Selected Top NNumber]);[Selected Top N Value])
)
)
)
 
The problem with this that the summation of the tables doesn't include the Other Category. Do you know what a soluction could be?
 
Thank you!
 
Regards, Hilbert
OwenAuger's avatar
OwenAuger
Super User
9 years ago

Hspuybroek

 

You can write the "Sales Amount Other" measure in my model without using EXCEPT, but ALLEXCEPT does something different so isn't appropriate here.

 

I'm not sure whether your version of Tabular allows variables or not, so here are two versions of the "Sales Amount Other" measure with and without variables:

 

Sales Amount Other NO EXCEPT WITH VARIABLES = 
VAR TopCustomers =
    TOPN ( [TopN Selection], ALL ( Sales[Customer] ), [Sales Amount] )
RETURN
    CALCULATE (
        [Sales Amount],
        KEEPFILTERS (
            FILTER (
                ALL ( Sales[Customer] ),
                NOT ( CONTAINS ( TopCustomers, Sales[Customer], Sales[Customer] ) )
            )
        )
    )

Sales Amount Other NO EXCEPT WITHOUT VARIABLES = 
CALCULATE (
    [Sales Amount],
    KEEPFILTERS (
        FILTER (
            ALL ( Sales[Customer] ),
            NOT (
                CONTAINS (
                    TOPN ( [TopN Selection], ALL ( Sales[Customer] ), [Sales Amount] ),
                    Sales[Customer], Sales[Customer]
                )
            )
        )
    )
)

Regards,

Owen

  • Hspuybroek's avatar
    Hspuybroek
    Frequent Visitor
    9 years ago

    OwenAuger Thanks for your help. But the following is empty now:

     

    Sales Amount Other Total Only =
    IF ( NOT ( ISFILTERED ( Sales[Customer] ) ); [Sales Amount Other] )

     

    I saw in your example that it has totals and every else is empty, but in my example everything is empty even the totals. I used your example: Sales Amount Other NO EXCEPT WITHOUT VARIABLES.

     

    The formula works:

    Uitval Top Overig = CALCULATE([Selected Top N Value];

    KEEPFILTERS(
    ( FILTER (
    ALL('ANG Fouten'[LPP_NAME]);
    NOT (CONTAINS(
    TOPN([Selected Top NNumber]; ALL('ANG Fouten'[LPP_NAME]);[Selected Top N Value]);'ANG Fouten'[LPP_NAME];'ANG Fouten'[LPP_NAME])
    )
    ))))

     

    But not the formula to get to total only.

     

    Could you help me?

     

    Regards, Hilbert

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    Hspuybroek

    As this thread is already closed, for better response, please post a new thread for your question. Thanks for your understanding. :)