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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
qngu
Frequent Visitor

How to de-mean Bar Chart?

Hi All,

 

From the example bar chart below, the blue dotted line is the average line. (-16+-9+...)/10 = -7. Instead of plotting the average as a line, I would like to subtract (-7), to all the y-values in the bar chart and the y-values are measures. For example, the value I want for decile 1 is -16-(-7)=-9. 

qngu_0-1657301265867.png

 

Any help greatly appreciated, Thanks.

qngu

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

Ok, that makes way more sense. 

Try:

 

Values minus mean for measure 1 =
VAR _Mean =
    AVERAGEX ( ALLSELECTED ( Table[Decile] ), [Your measure] )
RETURN
    IF ( NOT ( ISBLANK ( [Your Measure] ) ), [Your Measure] - _Mean )

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

6 REPLIES 6
PaulDBrown
Community Champion
Community Champion

In that case: 

Values minus mean for measure 1 =

VAR _Mean = DIVIDE([Measure 1] + Measure 2] + Measure 3] ...and so on upto Measure 10] , 10)

RETURN

[Measure 1] - _Mean

 

 

and repeat for each of the measures

 

You can't make it dynamic with your structure





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Sorry, I mis-understood you when you said each bar is a measure. Each bar is is calculated using a single measure. A simple example:

Table1

Date         | Value | Decile

1/1/2000  | -8      | '1'

1/1/2001  | -8      | '1'

1/1/2000  | -4.5   | '2'

1/1/2001  | -4.5   | '2'

....

 

Measure_1 = sum(Value) is set as the y-values and the x-values is set with the Decile column. So for decile 1, the measure will be -8+-8=-16, which is what we see in the bar chart and so on for each decile.

 

So there's only 1 measure, not 10. Sorry for the mis-understanding.

PaulDBrown
Community Champion
Community Champion

Ok, that makes way more sense. 

Try:

 

Values minus mean for measure 1 =
VAR _Mean =
    AVERAGEX ( ALLSELECTED ( Table[Decile] ), [Your measure] )
RETURN
    IF ( NOT ( ISBLANK ( [Your Measure] ) ), [Your Measure] - _Mean )

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Awesome. Thanks for the solution, I was looking for this and accepted this as the solutin as well.

PaulDBrown
Community Champion
Community Champion

Is each bar a measure then? If so, will there only ever be 10 data points?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Yes, each bar is a measure. Yes, there will only ever be 10 data points in this case. I'm hoping that it can be generalized to the number of categories on the X-axis though.

 

Thanks for taking a look,

qngu

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.