Stacked bar chart center aligned (variables average)

[bishophim]

My sample dataset contains people from different teams (the filter below), and I want to show each team member's performance compared to the average line. 

I read a post 4 years ago, it was wonderful but I couldn't fully copy it.

https://community.fabric.microsoft.com/t5/Desktop/Horizontal-bar-charts-with-custom-center-line/td-p...

When I put the average revenue measure in, all data go to "label below 100" (light blue coloured for easy reading)

average revenue measure = [Sales_revenue] / COUNT('sales_teams'[sales_agent])

I think it is because the measure itself is already variable, it won't let me use the total average as the reference.

(as you see different people have a different avg revenue)

I am not quite sure which step I've gone wrong. 

Is there any way I can use a team average revenue as the centre point, but each team will have a different average? 

[danextian · Accepted solution]

Hi @bishophim 

What do you mean by it wont "it won't let me use the total average as the reference"? The vertical line is at around 219K which is the average.

[Anonymous · Accepted solution]

Thank you for your prompt reply! danextian 

Hi @bishophim 

You can try changing "Teamavg_revenue" to the following version.

Teamavg_revenue = 
CALCULATE (
    AVERAGEX ( VALUES ( sales_pipeline[sales_agent] ), [Sales_revenue] ),
    ALLSELECTED ()
)

Best Regards,

Jayleny

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

[danextian · Accepted solution]

Hi @bishophim 

What do you mean by it wont "it won't let me use the total average as the reference"? The vertical line is at around 219K which is the average.

[bishophim]

Hi, thanks for the reply and apologise for the confusion.

I was talking about the graph on the left, which I wanted to use the average as centre point, like below:

When I followed the instructions from the post link, it gave me a blank visual, so I turned the label colour on and found all visuals are on Label below 100, which is not what I want to have. 

[danextian]

Assuming the length of the bars are the same as the actual, please see the attached pbix.

[bishophim]

Hi, many thanks for the solution. I tried to copy and paste and replace all elements, however it still seems not working in my file.

I doubt the problem is from the "Teamavg_revenue" measure, but not sure where it went wrong.

Would you mind to have a look to my dataset?

Stacked Bar.pbix

[danextian]

hi @bishophim 

Can you plase accept my post as solution as I was the one who actually provided it?

[danextian]

AVERAGEX  excludes any column value that doesn't have its respective value. Yours is simply dividing total sales by the count of agents? Are you supposed to include agents without sales in the average?

[danextian]

You're pointing your measures to the incorrect average. Use the measure that came with my pbix, not the one you initially created..