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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lizzy
Helper I
Helper I

Average instead of total in matrix

I've read a few previous threads on this topic, but can't quite find the right answer.

I currently have a data table that looks something like this

LocationSalespersonCustomer ID
1A1
2B2
3C3
1A4
1B5
1C6
2C7
3C8
2B9
1A10
3B11

and a corresponding matrix that counts the distinct number of numeric customer IDs per salesperson and location:

 123
A300
B123
C113

 

I would like to display an average for every row and column, eg

 123AVG
A3001
B1232
C1131.666667
AVG1.666667121.555556

How Do I go about doing this?

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@lizzy try this measure

Avg = 
AVERAGEX ( 
SUMMARIZE( Customer, Customer[Salesperson], Customer[Location] ), 
CALCULATE( DISTINCTCOUNT( Customer[Customer ID] ) ) 
)

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

2 REPLIES 2
parry2k
Super User
Super User

@lizzy try this measure

Avg = 
AVERAGEX ( 
SUMMARIZE( Customer, Customer[Salesperson], Customer[Location] ), 
CALCULATE( DISTINCTCOUNT( Customer[Customer ID] ) ) 
)

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

 That measure was perfect, thanks.

I'm now having a slightly different problem related to this. Some of the cells have no entries and are showing up as blank, not zero, so the average is only including the non-blank cells, which is wrong. I feel like I should be able to fix this by adding a +0 somewhere in the measure, but nothing seems to work. I also tried replacing the DISTINCTCOUNT with

 

 

IF(ISBLANK(DISTINCTCOUNT( Customer[Customer ID] )),0,DISTINCTCOUNT( Customer[Customer ID]  ))

 

 

which also left the blank in place. What should I do to return 0 from the count or have the average treat a blank as 0?

 
Edit: @parry2k not sure if you saw this followup. I'd appreciate any insight you have.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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