Top N + Others using sum of columns

[fa82]

Hi Everyone,

I'm trying to get a dynamic top N + Others rows in matrix using a sum of the columns

My table has: Customer, Category, Value

And matrix looks like this:

Is there a way to display the top 5 customers + 'others';  Top 5 being determined by a sum of the values in all categories A-D for each customer?

[Anonymous · Accepted solution]

Hi @fa82 ,

If you want to add Category to Matrix visual as well, please follow these steps.

1. Create a rank measure:

Rank = RANKX(ALL('Table'), CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Customers])),,DESC,Dense)

2. Add a new table, do not need to build relationships.

Table 2 = 
var _t1= UNION( VALUES('Table'[Customers]),ROW("Customers","Others"))
var _t2=VALUES('Table'[Category])
return CROSSJOIN(_t1,_t2)

3. Create a measure:

Measure =
VAR _t =
    SUMMARIZE ( FILTER ( 'Table', [Rank] <= 5 ), [Customers] )
VAR _value =
    SWITCH (
        MAX ( 'Table 2'[Customers] ),
        "Others",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( 'Table', [Rank] > 5 && [Category] = MAX ( 'Table 2'[Category] ) )
            ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                [Customers] = MAX ( 'Table 2'[Customers] )
                    && [Category] = MAX ( 'Table 2'[Category] )
            )
        )
    )
VAR _sum =
    IF (
        MAX ( 'Table 2'[Customers] ) = "Others",
        CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', [Rank] > 5 ) ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', [Customers] = MAX ( 'Table 2'[Customers] ) )
        )
    )
RETURN
    IF (
        MAX ( 'Table 2'[Customers] )
            IN _t
                || MAX ( 'Table 2'[Customers] ) = "Others",
        IF ( HASONEVALUE ( 'Table 2'[Category] ), _value, _sum ),
        BLANK ()
    )

Final output:

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

[fa82]

Your solution worked perfectly, thank you!

Bonus question: Is there a way to ensure the 'others' row appears last? 

[PC2790]

I had a similar problem and I found this video quite simple to use.

See if it covers your requirement

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.