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 ...
PattemManohar
Community Champion
Community ChampionAnonymous Please post the sample data (copiable format) and expected output to understand your scenario in detail. It will be helpful to provide an accurate solution.
how do I do that in copiable format?
- PattemManohar
Anonymous 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