Show top 5 values, sum top 5 vales, sum others, sum totals

[cerebro]

Hi everybody,

i would like to create the following table in Power BI:
the problem is that it's either the top 5 and their sum or top5 agreggated, others and the totals. Is there any possibility to make any structure with subtotals work? I have some sample data but i am not sure how to upload it here.

Thanks in advance

[Anonymous · Accepted solution]

Hi @cerebro ,

You can create a seperate table by entering data as follows.

Then create a calculated table

Table 2 = UNION(SELECTCOLUMNS(FILTER('Table',[topn]<=5),"Category",[Customer]),'Table (2)')

Create the measure

Values = 
IF (
    MAX ( 'Table 2'[Category] ) = "sum of top5",
    CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', [topn] <= 5 ) ),
    IF (
        MAX ( 'Table 2'[Category] ) = "others",
        CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', [topn] > 5 ) ),
        IF (
            MAX ( 'Table 2'[Category] ) = "total",
            CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table' ) ),
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( 'Table', [Customer] = MAX ( 'Table 2'[Category] ) )
            )
        )
    )
)

Best Regards,

Stephen Tao

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

[cerebro]

thank you very much for this solution!

[amitchandak]

@cerebro , Try like

https://www.youtube.com/watch?v=UAnylK9bm1I

TOPN with other

https://www.proserveit.com/blog/ms-power-bi-topn-and-other

[cerebro]

i got everything except for the the sum of top 5

because i cant add it as a line to the column with customer names.

I created a column that indicates the names of top 5 customers and every other customer receives the "other" lable. But i dont know how to create a top 5 sum in the same table