Count of average score - shown as a bar chart

[paulsmit]

Hi,

I have a set of data which contians customer satisfaction scores for a range of different locations and I need to create a bar chart which gives a count of locations for each average satisfaction score e.g. 3 locations may have an average score of 80 and 8 locations may have an average score of 95.  I would expect to see this as 2 blocks on the graph, one with a height of 8 at the 95 mark and one with a height of 3 at 80.   

I would like the x-axis to show average satisfaction scores (as whole numbers between 1-100) and the y-axis to be a count of the number of properties.

Any help would be much appreciated.

Cheers

Paul

[LivioLanzo]

Hi @paulsmit are you able to show a sample of your data???

[paulsmit]

Yeah sure. Location Satisfaction ------------ -------------- Derby 90 Derby 95 Derby 80 Chesterfield 80 Chesterfield 86 Chesterfield 90 Mansfield 50 Mansfield 60 Sheffield 60 Sheffield 50 I would expect to see a block of 1 high at 88 (the average for Derby), a block of 1 high at 85 (representing Chesterfield) and a block of 2 high at 55 (representing Mansfield & Sheffield). Cheers Paul

[paulsmit]

Apologies. That didn't format as I'd expected.

[v-yuta-msft · Accepted solution]

@paulsmit,

Firstly create a calculate column average to achieve the average value every location:

Average = CALCULATE(AVERAGE(Table1[Satisfaction]), ALLEXCEPT(Table1, Table1[Location]))

Then create a measure to achieve the distinct count every average value:

Count = CALCULATE(DISTINCTCOUNT(Table1[Location]), ALLEXCEPT(Table1, Table1[Average]))

Finally drag Average to X-axis, Count to Value field, the result is like below:

 

Regards,

Jimmy Tao

[paulsmit]

Thanks for your help with this. It works perfectly,