Forum Discussion
Adding a Total Column to a Matrix Visual that only sums spcific columns
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 |
Community SupportHi 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
you made a table visual. I need a matrix visual that has Action in columns and Custid and Date (in real solution, there are more criteria like country, gender etc) in rows. rows are drillable.
So, if I have a matrix that filters on rows AND columns, I guess it is not possible to have extra colums for measures that only filter on row criteria, but is independant from column criteria. Or is there a solution?
My solution now is:
I added an extra column in Query Editor that is always 1. Then pivoted the column Action (using the always 1 column as value) and use the new columns (one for each distinct value in Action) in data section. Leave column section empty. Then I can add measures to the data section and they get also new columns.
- v-yuta-msft
Hi DevOn99,
Have you solved your issue currently? If you have, could you kindly mark my answer to finish this thread?
Regards,
Jimmy Tao
Can you please elaborate your answer? I am facing the same issue now. Thanks.