Dax to group the non-bottom 5 of a table.

[ROG]

Hello guys!

Below the Top 5 companines with the lowest number of sales, which means bottom 5.

And used the filter pane to get that.

Now I need another table showing only one row with "Others", which means the remaining companies. All companies excluding the bottom 5, grouped by "Others". What would be the DAX for this?

Thanks!

[v-xinruzhu-msft · Accepted solution]

Hi @ROG 

You can refer to the following solution.

Sample data 

1.Create a calculated table 

Company = UNION(SUMMARIZE('Table',[Company]),{"Others"})

2.Create a measure

Measure =
VAR a =
    SUMMARIZE ( ALLSELECTED ( 'Table' ), [Company], "Sum", SUM ( 'Table'[Sales] ) )
VAR b =
    SUMMARIZE ( TOPN ( 5, a, [Sum], ASC ), [Company] )
RETURN
    IF (
        SELECTEDVALUE ( 'Company'[Company] ) IN b,
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            'Table'[Company] IN VALUES ( 'Company'[Company] )
        ),
        IF (
            SELECTEDVALUE ( 'Company'[Company] ) = "Others",
            CALCULATE ( SUM ( 'Table'[Sales] ), NOT ( 'Table'[Company] IN b ) )
        )
    )

Then put the field of the copany table to the row and put the date of the original to the column, then put the measure to the value

Output

Best Regards!

Yolo Zhu

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