Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Location | Salesperson | Customer ID |
1 | A | 1 |
2 | B | 2 |
3 | C | 3 |
1 | A | 4 |
1 | B | 5 |
1 | C | 6 |
2 | C | 7 |
3 | C | 8 |
2 | B | 9 |
1 | A | 10 |
3 | B | 11 |
and a corresponding matrix that counts the distinct number of numeric customer IDs per salesperson and location:
1 | 2 | 3 | |
A | 3 | 0 | 0 |
B | 1 | 2 | 3 |
C | 1 | 1 | 3 |
I would like to display an average for every row and column, eg
1 | 2 | 3 | AVG | |
A | 3 | 0 | 0 | 1 |
B | 1 | 2 | 3 | 2 |
C | 1 | 1 | 3 | 1.666667 |
AVG | 1.666667 | 1 | 2 | 1.555556 |
How Do I go about doing this?
Solved! Go to Solution.
@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.
@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?
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |