Forum Discussion
Creating matrix visual using values as row header/value
Hi All,
I am stuck on creating a visual using Matrix.
Sample Data:
| Case No. | Sales by | Being Assistant 1 | Being Assistant 2 |
| 1A | Person A | Person B | Person C |
| 2B | Person A | Person B | Person C |
| 3C | Person B | Person C | Person D |
| 4D | Person B | Person C | Person D |
| 5E | Person C | Person D | Person A |
| 6 | Person C | Person D | Person A |
| 7 | Person D | Person A | Person B |
I am trying to translate on how many case for a person have done for each "sales by", "being assitant 1" and "being assistant 2".
Intended Matrix:
| 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 |
Appreciate all the help on this.
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]
5 Replies
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.
Column_Person = DISTINCT(UNION(DISTINCT('Table'[Sales By]),DISTINCT('Table'[Being Assistant 1]),DISTINCT('Table'[Being Assitant 2])))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]
