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: customer amount A 1 B 2 C 3 D 4 E 5   and i woul...
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.

     

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.