cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate I
Advocate I

Average Line on Scatter Displaying Wrong Value

Hi

 

My Average Line on my Scatter Chart is set to use a Volume YoY % measure ( :=SUM ( TY VOL ) / SUM ( LY VOL ) - 1 ).

 

Under Details I have the Business dimension, so my Scatter is showing YOY % Volume by Business.

 

However, my Average Line is displaying the arithmetic mean of the individual Business YoY %'s instead of the value returned by the YoY % measure. So the Average Line is using my measure, but instead of taking its evaluated value (given what's visible in the scatter) its simply averaging each YoY % for each visible Business.

 

How can I achieve an Average Line which uses the correct measure value? (this looks like a bug).

 

Thank you.

 

 

4 REPLIES 4
Microsoft
Microsoft

@mmuddasar

 

I’m not so clear about your problem, could you please give a screenshot of your Scatter chart and its Fields?

 

Best Regards,

Herbert

Average Line.JPG

 

 

@mmuddasar

 

The calculation method of the Average Line should be like the following DAX formula. So the Average Line is averaging each YoY % for each visible Business.

Avg = 
AVERAGEX ( ALL ( Table1 ), [YoY % measure] )

 

To get your expected Avg result, we need to use the DAX formula like below. But we can only show it in some other visuals (e.g. Line chart) using a separate line, because it will clear all filters.

Avg_All = 
 (
    CALCULATE ( SUM ( Table1[2016 Weeks] ), ALL ( Table1 ) )
        / CALCULATE ( SUM ( Table1[2015 Volume] ), ALL ( Table1 ) )
        - 1
)

Average Line on Scatter Displaying Wrong Value_1.jpg

 

It seems not to be a bug but an expected logic in current version. I found a same idea here, if you have the same desire, you can vote it.

 

Best Regards,

Herbert

Firstly, thank you for taking the time to advise! I really appreciate it.

 

Yes I have been trying to think of a suitable DAX formula to achieve the result I wanted, but the scatter at present doesn't allow us to use any other measures than those already plotted on the scatter.

 

An alternative would be to allow the user to specify that a measure being used by an Average Line is of type ratio so it would compute its value appropriately.

 

And yes it was unfair of me to describe it as a bug since I can see why it behaves so; for example if I put [2016 Weeks] on the scatter and add Average Line, it will give me correct average.

 

The idea you found was posted by me. Scatter plots are really powerful visualisations and I hope in future releases they allow us to customise them more. For now I'll continue to use Median Line instead on scatter. At least median is arguably more representative of the 'average'.

 

Thanks for the helpful reminder about iterator functions!

 

Kind regards

Mohammed

 

 

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors