Power BI Report Builder - Sum of a group in a new column of a table

[Anonymous]

Hi there, 

I'm struggling with summing up the values of a group within a table. The group only displays the Bottom N of a category and I only want the sum of them and not the grand total of the whole category. Idealy I would like to have a column with the sum of this group i.e. the sum of the Bottom N.

Is there any way of doing this without changing the raw data?

Thank you

Anna

[Anonymous · Accepted solution]

Hi @Anonymous ,

Botton N can be converted to TOP N in reverse order, and then we create a ranking measure first.

Rank = RANKX(FILTER(ALLSELECTED('Table'),[ID]=MAX('Table'[ID])),CALCULATE(SUM('Table'[Value])),,ASC,Dense)

Create another meausre to get the sum of top 2.

Top2Sum =
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[ID],
        "Top2Sum",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( ALLSELECTED ( 'Table' ), [ID] = MAX ( 'Table'[ID] ) && [Rank] <= 2 )
            )
    )
RETURN
    SUMX ( _table, [Top2Sum] )

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.