cancel
Showing results for
Did you mean:
Regular Visitor

## 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.

1 ACCEPTED SOLUTION
Super User

@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]``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Super User

@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]``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Regular Visitor

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
Super User

@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]``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Regular Visitor

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?

 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

Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors