Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Zakk2000
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 1Being Assistant 2
1APerson APerson BPerson C
2BPerson APerson BPerson C
3CPerson BPerson CPerson D
4DPerson BPerson CPerson D
5EPerson CPerson DPerson A
6Person CPerson DPerson A
7Person DPerson APerson 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:

PersonSales byBeing Assitant 1Being Assistant 2Total Cases
Person A2125
Person B2215
Person C2226
Person D125

 

 

Appreciate all the help on this.

 

 

1 ACCEPTED SOLUTION

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
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]

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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])
)
)

 

PersonSales byBeing Assitant 1Being Assistant 2Total Cases
Person A2125
Person B2215
Person C2226
Person D125

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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:

PersonSales byBeing Assitant 1Being Assistant 2Total Cases
Person A2125
Person B2215
Person C2226
Person D125
Total12 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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.