Reply
Highlighted
Frequent Visitor
Posts: 6
Registered: ‎12-12-2018
Accepted Solution

Count of average score - shown as a bar chart

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

 

 


Accepted Solutions
Community Support Team
Posts: 2,510
Registered: ‎02-06-2018

Re: Count of average score - shown as a bar chart

@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:
Capture.PNG 
 
Regards,
Jimmy Tao

View solution in original post


All Replies
Super User
Posts: 761
Registered: ‎09-16-2018

Re: Count of average score - shown as a bar chart

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


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


Proud to be a Datanaut!

Frequent Visitor
Posts: 6
Registered: ‎12-12-2018

Re: Count of average score - shown as a bar chart

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
Frequent Visitor
Posts: 6
Registered: ‎12-12-2018

Re: Count of average score - shown as a bar chart

Apologies. That didn't format as I'd expected.
Community Support Team
Posts: 2,510
Registered: ‎02-06-2018

Re: Count of average score - shown as a bar chart

@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:
Capture.PNG 
 
Regards,
Jimmy Tao
Frequent Visitor
Posts: 6
Registered: ‎12-12-2018

Re: Count of average score - shown as a bar chart

Thanks for your help with this. It works perfectly,