Power BI DAX : Get sum of a column based on another

Question

Let say I have the folowwing table :

Client    Sales
A         1000
A         100
A         10
B         1000
B         10
C         1

I would like to add another column Names TotalClient at the end which would the new table look like this :

Client    Sales   Total
A         1000    1110
A         100     1110
A         10      1110
B         1000    1010
B         10      1010
C         1       1

Is this possible either in DAX or in the query editor ? I tried many ways, but still can't figure out how.

I was able to get information I needed using the SUMMARIZE function to get another table, but I run into other problem in term of security. It seems the data table gets filtered right, but not the summarized version.

Thanks

Alexis Olson Alexis Olson · Accepted Answer · 2018-11-07T19:39:59

You just need to know how to use CALCULATE to adjust your filter context.

Total = CALCULATE( SUM( Table1[Sales] ), ALLEXCEPT( Table1, Table1[Client] ) )

This says to calculate the sum of the sales for all rows in the table where we've removed any row context except for the client. Thus you get the sum over all rows where the client matches the client in the current row.

If you only have these columns, you can do this

Total = CALCULATE( SUM( Table1[Sales] ), ALL( Table1[Sales] ) )

This removes only the Sales row context and leaves the Client row context. This probably will not work as expected if you have other columns as well since they will still be part of the row context.

You can also remove all filter context with ALL and then explicitly add back in the filtering you want:

Total =
CALCULATE(
    SUM( Table1[Sales] ),
    ALL( Table1 ),
    Table1[Client] = EARLIER( Table1[Client] )
)

or do similarly with SUMX instead

Total =
SUMX(
    FILTER(
        Table1,
        Table1[Client] = EARLIER( Table1[Client] )
    ),
    Table1[Sales]
)

Lots of ways to skin the cat.

Power BI DAX : Get sum of a column based on another

1 Answers