average line

[liping_qin]

As could be seen from the screenshot, the value of an average line (232, the red line) is not the same as the average value on a table (140.10, on the top right), with the same data set of 448968 data points.

[brjones]

This problem is not solved!!

The reporter said that the average line was displaying the wrong value.  I have the exact same problem with my line charts where I use the built in Average Line feature from PBI.

The "solution" posted did not fix the problem of the average line not working - it simply told the OP to just stop using the built in average line.  Yes, there was a workaround posted, but the way the average line works is flawed.

If you have a table that calculates an average based on a data set, and the visualization pulls from that same data set and yet somehow comes up with a different average, then the problem still exists.

PBI, please fix this.

[liping_qin]

Agree with brjones Hope this could be fix, and have the average line in PBI works without workaround.

[braaten]

I second the request by brjones.  I just discovered this problem and can confirm it is not yet fixed with the latest November 2017 release.  Please add this to your "must fix" list.  Was this working before?  I don't remember noticing this problem.

Thanks.

[Anonymous]

Are you able to post more details of the set up of this?  Its pretty difficult to guess what might be going wrong here.  Are these lines coming from Measures?  Can you post the Dax code of these measures?  What can you tell us about the report these are on? What filters and context are you using?

[liping_qin]

Hi Ross, 

Thanks very much for helping here. I tried to attached the sample dataset, but didn't find where to attach.

We didn't write Dax code, but import a excel dataset and drag and draw the charts.  Here's the setting, the first one for the value on Table, and second one for the Average Line on Line Chart.

[GilbertQ]

Hi @liping_qin

Could you perhaps create a new measure which will be the Sum of your data.

Then when you create your Average Line, from the Measure drop down, select the average measure you just created.

[liping_qin]

Hi @GilbertQ,

Thanks for the workaround, I got the same value on the Average Line by creating a measure as (Measure = SUM('TREP_0426 updatestotalRatePerSe'[Update Rate]))  and select this measure when adding Average Line.

[GilbertQ]

Hi @liping_qin

What if you change the measure from a SUM to an AVERAGE?

[liping_qin]

Hi @GilbertQ,

This time I got an average line of value 137, still not the expected 140.10, not sure what's wrong with the Average Line function.

And here's the sample data you could used to reproduce the issue.  

https://1drv.ms/u/s!Auczg22cCvWx1wlvMZJGet7EOFz1 

We tried a sample of 20 data points with one point's value as 0, the value of Average Line is the same as that calculated by Excel Average function.

Not sure if our issue is caused by large number of data points. 

[GilbertQ]

Hi @liping_qin

Thanks for the link but I cannot extract it from the GZ format.

Is it possible to change to .zip or even upload the PBIX file?

[liping_qin]

Hi  @GilbertQ,

Thanks for your support. Here's the data and pbix file 

https://1drv.ms/f/s!Auczg22cCvWx1wpPRmRVnDFPFwTU

[GilbertQ · Accepted solution]

What you could do is to create the following measure which will then create the line across your dataset

Average Line = 
CALCULATE (
    DIVIDE ( SUM ( '0426'[Update Rate] ), COUNTROWS ( '0426' ) ),
    ALL ( '0426' )
)

And here is what it looks like once completed

And here is the updated file here: https://1drv.ms/u/s!Apxn-69XhcAmhqsVDkaulZTJN9sztA

[liping_qin]

Hi @GilbertQ,

Brilliant!  It works.

I learnt a lot from your solution.  Supper thanks ^_^

[GilbertQ]

Hi @liping_qin

Glad to assist.