Let say I have the following 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 make 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.
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.