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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JPux
Frequent Visitor

Display (and count) the distribution of values in a table based on a criteria?

Hi all,

This must be simple but I struggle to find the way to do it, so I'd appreciate any help / guidance you could provide.

 

I have an initial table (example below) with the ratings given by the sales team about several products (i.e. a sales rep from Europe rates Product A as "1" in an 0-3 scale). For a given product in a region I need to display the distribution of ratings received together with the total average of all the ratings, and show it using a graphic like this (I've done it with Excel):

 

JPux_2-1671054192213.png

 

To do so I need to:

a) count the number of times each rating is given per product in a region (by all sales reps)

b) calculate the average rating given to a product in each region (by all sales reps)

 

I've done b) easily by creating a reference table in in QueryEditor and using the "Group by ..." option, but I struggle doing "a)" and creating the visual.

 

Any help highly appreciated.

Thanks in advance,

J

 

>> Data samples in case the explanation above is confusing:

 

* Data Table

RegionProductRating
Europe  Prod A   3
EuropeProd A3
EuropeProd A2
EuropeProd A1
EuropeProd B3
EuropeProd B2
EuropeProd B2
EuropeProd B0
AsiaProd A1
AsiaProd A0
AsiaProd B2
AsiaProd B1

 

* Number of times each rating is given to ProdA in Europe would be:

Rating 0:  0 times

Rating 1: 1 times

Rating 2: 1 time

Rating 3: 2 times

 

* Average rating given to ProdA in Europe would be:

Average rating: (3+3+1+2)/4= 2.25

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @JPux ,

 

To do this you can do the following:

  • Create a table with the Ratings from 0 to 3
  • Add the following measures to do the count and the average:

 

TotalRatings = COUNTROWS(FILTER(Rating, Rating[Rating] in VALUES(RatingsVALues[Rating]))) + 0

Average Values = AVERAGE(Rating[Rating])
  • Create a scatter plot with the following setup:
    • X-Axis - Rating from the new table: Dont summarize
    • Y-Axis - Measure TotalRatings
    • X-axis constant line: Average  Values

 

You can also add some additional measure for the axis to be perfect visible 

 

Result below:

MFelix_1-1671134558612.png

 

 

I know this is not the dot that you present but show a value for the average values and you can see what is higher and lower than your average.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @JPux ,

 

To do this you can do the following:

  • Create a table with the Ratings from 0 to 3
  • Add the following measures to do the count and the average:

 

TotalRatings = COUNTROWS(FILTER(Rating, Rating[Rating] in VALUES(RatingsVALues[Rating]))) + 0

Average Values = AVERAGE(Rating[Rating])
  • Create a scatter plot with the following setup:
    • X-Axis - Rating from the new table: Dont summarize
    • Y-Axis - Measure TotalRatings
    • X-axis constant line: Average  Values

 

You can also add some additional measure for the axis to be perfect visible 

 

Result below:

MFelix_1-1671134558612.png

 

 

I know this is not the dot that you present but show a value for the average values and you can see what is higher and lower than your average.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



JPux
Frequent Visitor

wow! this is exactly what I needed.

It works perfect. (And I guess I've just realized I did not really understand how to use Measures in PBI. 🙈)

 

Muito obrigado @MFelix

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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