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
Anonymous
Not applicable

Clustered Column Bar Graph - Analytics Tab - Calculating Avg. on Avg.

Hello everybody 🙂

I was requested to create a new dashboard in Power BI to show the average Customer Satisfaction Index (CSI - 1 to 10) per Brand and Salesperson. The idea is to have one graph for the Brands and one for the personnel (Brand would be 1 level of hierarchy above the Salesperson given that each Brand will have multiple Salesperson associated to them).

 

Below is a picture of the current state of the clustered charts (I had to cut out the names of the personnel). The problem appears when I must show an horizontal line showing the average of the brands and the average of the personnel. Has you can see, the average is different. I believe that this is because it first calculates the average for each salesperson and afterwards it will calculate the average of all the Salespersons, this will cause it to not reflect the fact that there are different volumes of customers for each Salesperson.

 

Currently the average is calculated with a simple measure: Avg. CSI = AVERAGE(Satisfaction Rating)

I used the Analytics Tab to create the horizontal lines.

 

Any help would be much appreciated, thank you.Avg. Of Avg. Analytics.PNG

 

 

 

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

Hi @Anonymous ,

 

You can create a new column to calculate average. The aim of it is to make the average sources consistent.

AVG =
CALCULATE (
    AVERAGE ( 'Table'[CSI] ),
    ALLEXCEPT ( 'Table', 'Table'[Band], 'Table'[Person] )
)

Then you need to use line and clustered column chart, put the “AVG” to the “line value”.

4-1.PNG4-2.PNG

 

Best Regards,

Eads

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

4 REPLIES 4
v-eachen-msft
Community Support
Community Support

Hi @Anonymous ,

 

You can create a new column to calculate average. The aim of it is to make the average sources consistent.

AVG =
CALCULATE (
    AVERAGE ( 'Table'[CSI] ),
    ALLEXCEPT ( 'Table', 'Table'[Band], 'Table'[Person] )
)

Then you need to use line and clustered column chart, put the “AVG” to the “line value”.

4-1.PNG4-2.PNG

 

Best Regards,

Eads

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

Hello Eads,

 

Thank you very much for your help.

 

Unfortunately this solution creates a problem in the visual.

 

Bellow is a picture of the reulting Graph (I replaced the name of the salesperson with the location of the store so that there wouldn't be a problem uploading the image).

 

Despite the value being the same (I checked the decimal places, the values are exactly the same) the line fluctuates, I have already seen some users reporting this issue and I believe Power BI team is already trying to solve the situation.

 

I will keep checking for an alternate solution and post it here for other users, if by any chance you have another sugstion please let me know.

 

Either way thank you very much for your help 🙂

 

Visual Error.PNG 

hi @Anonymous ,

 

Just go to y axis format pane and turn on secondary and then turn on align zeros.

 

2019_09_02_14_58_58_Cashflow_Power_BI_Desktop.png

Anonymous
Not applicable

Thank you so much mussaenda 😄 it's  perfect now!

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.