Forum Discussion
Adding a Total Column to a Matrix Visual that only sums spcific columns
I have a table that has (simplified) the columns CustID, Action, Date I then have a matrix visual that has Action as Columns, CustID AS Rows and Count(Action) as Values. Now I need an extra column ...
how do I do that in copiable format?
PattemManohar
Community Champion
Community ChampionAnonymous I mean the sample data that can be copied. Not like screenshots or image format.
This is the simplified data structure:
This is the table:
Date CustId Action 01.01.2018 1 A 02.01.2018 1 B 03.01.2018 1 A 04.01.2018 1 C 05.01.2018 2 A 06.01.2018 2 A 07.01.2018 2 C 08.01.2018 2 C 09.01.2018 2 C 10.01.2018 2 D Expected outcome (I did this as a Matrix in PBI, but cannot see a way to add the Sum(A,B) column):
PIVOT Values are Count(Action) Action CustId A B C D Sum (A,B) 1 2 1 1 0 3 2 2 3 0 1 5 
v-yuta-msftCommunity Support
Hi DevOn99,
You you create measures using DAX formula like pattern below:
A = CALCULATE(COUNT(Table1[Action]), FILTER(ALLEXCEPT(Table1, Table1[CustId]), Table1[Action] = "A")) B = CALCULATE(COUNT(Table1[Action]), FILTER(ALLEXCEPT(Table1, Table1[CustId]), Table1[Action] = "B")) C = CALCULATE(COUNT(Table1[Action]), FILTER(ALLEXCEPT(Table1, Table1[CustId]), Table1[Action] = "C")) D = CALCULATE(COUNT(Table1[Action]), FILTER(ALLEXCEPT(Table1, Table1[CustId]), Table1[Action] = "D")) Sum(A, B) = CALCULATE(COUNT(Table1[Action]), FILTER(ALLEXCEPT(Table1, Table1[CustId]), Table1[Action] = "A" || Table1[Action] = "B"))
This result is like this:
Regards,
Jimmy Tao
The problem is, that the visual is a matrix and
- v-yuta-msft
Hi DevOn99,
I'm afraid the matrix in your post couldn't be realized because the SUM(A,B) is not a value in Action column, as a workaround, you can drag measure sum(A,B) above the value field lile below:
Regards,
Jimmy Tao