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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
paulsmit
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

@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

5 REPLIES 5
LivioLanzo
Solution Sage
Solution Sage

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

 


 


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


Proud to be a Datanaut!  

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

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

@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

Thanks for your help with this. It works perfectly,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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