cancel
Showing results for
Did you mean:
Frequent Visitor

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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

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
5 REPLIES 5
Super Contributor

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

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

Proud to be a Datanaut!

Frequent Visitor

## 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

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

Apologies. That didn't format as I'd expected.
Community Support Team

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

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
Highlighted
Frequent Visitor

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

Thanks for your help with this. It works perfectly,

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 140 members 1,610 guests
Recent signins: