Forum Discussion
Creating matrix visual using values as row header/value
Zakk2000 I was assuming that you would just use Sales by column in your matrix visual. If for some reason that is not feasible, you could create a new table like this (I would leave as a disconnected table).
Person Table = DISTINCT( UNION( SELECTCOLUMNS('Table',"Person",[Sales By]), SELECTCOLUMNS('Table',"Person",[Being Assistant 1]), SELECTCOLUMNS('Table',"Person",[Being Assistant 2]), ) )You would change the measures like this:
Sales By Measure = VAR __Person = MAX('Person Table'[Person]) VAR __Result = COUNTROWS(FILTER(ALL('Table',[Sales By] = __Person RETURN __Result Sales By Being Assistant 1 Measure = VAR __Person = MAX('Person Table'[Person]) VAR __Result = COUNTROWS(FILTER(ALL('Table'),[Being Assistant 1] = __Person)) RETURN __Result Sales By Being Assistant 2 Measure = VAR __Person = MAX('Person Table'[Person]) VAR __Result = COUNTROWS(FILTER(ALL('Table'),[Being Assistant 2] = __Person)) RETURN __Result Total Cases Measure = [Sales By Measure] + [Sales By Being Assistant 1 Measure] + [Sales By Being Assistant 2 Measure]
Zakk2000 Maybe:
Sales By Measure = COUNTROWS('Table')
Sales By Being Assistant 1 Measure =
VAR __Person = MAX('Table'[Sales by])
VAR __Result = COUNTROWS(FILTER(ALL('Table'),[Being Assistant 1] = __Person))
RETURN
__Result
Sales By Being Assistant 2 Measure =
VAR __Person = MAX('Table'[Sales by])
VAR __Result = COUNTROWS(FILTER(ALL('Table'),[Being Assistant 2] = __Person))
RETURN
__Result
Total Cases Measure = [Sales By Measure] + [Sales By Being Assistant 1 Measure] + [Sales By Being Assistant 2 Measure]
Hi Greg_Deckler ,
Your solution managed to do on everything for the values side, but I am still struggling on the 'Person' column. I tried to create a new table and merge the columns by distinct 'Person' name.
| Person | Sales by | Being Assitant 1 | Being Assistant 2 | Total Cases |
| Person A | 2 | 1 | 2 | 5 |
| Person B | 2 | 2 | 1 | 5 |
| Person C | 2 | 2 | 2 | 6 |
| Person D | 1 | 2 | 2 | 5 |
Zakk2000 I was assuming that you would just use Sales by column in your matrix visual. If for some reason that is not feasible, you could create a new table like this (I would leave as a disconnected table).
Person Table = DISTINCT( UNION( SELECTCOLUMNS('Table',"Person",[Sales By]), SELECTCOLUMNS('Table',"Person",[Being Assistant 1]), SELECTCOLUMNS('Table',"Person",[Being Assistant 2]), ) )You would change the measures like this:
Sales By Measure = VAR __Person = MAX('Person Table'[Person]) VAR __Result = COUNTROWS(FILTER(ALL('Table',[Sales By] = __Person RETURN __Result Sales By Being Assistant 1 Measure = VAR __Person = MAX('Person Table'[Person]) VAR __Result = COUNTROWS(FILTER(ALL('Table'),[Being Assistant 1] = __Person)) RETURN __Result Sales By Being Assistant 2 Measure = VAR __Person = MAX('Person Table'[Person]) VAR __Result = COUNTROWS(FILTER(ALL('Table'),[Being Assistant 2] = __Person)) RETURN __Result Total Cases Measure = [Sales By Measure] + [Sales By Being Assistant 1 Measure] + [Sales By Being Assistant 2 Measure]Hi Greg_Deckler ,
Your solution helps a lot 🙂
I am confuse with the error by my power BI.
The "total" (bolded) at the bottom of the table that generated by Matrix viz is only showing the last person (Person D). Is there a way to fix this?
Result received:
Person Sales by Being Assitant 1 Being Assistant 2 Total Cases Person A 2 1 2 5 Person B 2 2 1 5 Person C 2 2 2 6 Person D 1 2 2 5 Total 1 2 5 Zakk2000 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8