Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

TopN + other values

Hello,

 

I'm trying to create a topN + other values table. As a simple example, i have a table that looks like this:

customeramount
A1
B2
C3
D4
E

5

 

and i would like to have a table that produces the following result, for a selected value of TopN (for example N = 3)

 

customeramount
E5
D4
C3
Others3

 

Note that "Others" represent the sum of everything else that isn't in top 3

 

I am able to get the Top 3 table, as well as the value of Others through a new measure separately with the formula 

 

other calculation = CALCULATE([amount],topn(2, values(Sheet1[customer]), [amount],1))
 
but i don't know how to merge the two into a single table. Any help would be appreciated, thanks!
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Anonymous 

    To calculate Others you will need to create a Column with the expression like below.

     

     

    Rank Top 3 Products and Others = 
-- add this column to your customer table
VAR rnk = RANKX(
    'Product', -- replace with customer table
    CALCULATE(SUM(Sales[Quantity])),,, -- amount column
    Dense
) <= 3
RETURN 
IF(rnk, FORMAT('Product'[Product Code], ""), "Others") -- replace Product Code with Customer

    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

    To calculate Others you will need to create a Column with the expression like below.

     

     

    Rank Top 3 Products and Others = 
-- add this column to your customer table
VAR rnk = RANKX(
    'Product', -- replace with customer table
    CALCULATE(SUM(Sales[Quantity])),,, -- amount column
    Dense
) <= 3
RETURN 
IF(rnk, FORMAT('Product'[Product Code], ""), "Others") -- replace Product Code with Customer

    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Mariusz 

       

      Thank you for your help! The code worked perfectly, but i just have some questions about the code itself for better understanding,

       

      In the "Rank Top 3 Products and Others" code, what is the purpose of the FORMAT code?

       

      In the "Rank Top Three Sales" code, why do you need the IF statement after RETURN if there is no ResultIfFalse?
       
      Thank you.

       

       

      • Mariusz's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi Anonymous 

         

        FORMAT(123 ,"") will return 123 as text.
        IF without third argument will produce BLACK , when added to a visual it will produce only top 3.

         

        Hope this helps
        Mariusz

         

         

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    HI Anonymous,

    You can use the expresion below.

    Rank Top Three Sales = 
VAR rnk = RANKX(
    CALCULATETABLE(
        VALUES('Product'[Brand]), --Replace with your customer 
        ALLSELECTED()
    ), 
    [Sales],, --Replace with your amount 
    DESC
) <= 3
RETURN IF(rnk, [Sales]) --Replace [Sales] with your amount

    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.