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

[JPux]

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

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

Region	Product	Rating
Europe	Prod A	3
Europe	Prod A	3
Europe	Prod A	2
Europe	Prod A	1
Europe	Prod B	3
Europe	Prod B	2
Europe	Prod B	2
Europe	Prod B	0
Asia	Prod A	1
Asia	Prod A	0
Asia	Prod B	2
Asia	Prod B	1

* 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

[MFelix · Accepted solution]

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:

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.

[JPux]

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