cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
liping_qin
New Member

average line

 

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.

 

 

AverageLine.PNG

 

 

1 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

 

Delete 1.png

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

14 REPLIES 14
brjones
Advocate I
Advocate I

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.

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

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

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?

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.

 

AverageFunctionOnTableValue.PNGAverageLine.PNG

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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.

Hi @liping_qin

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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. 

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi  @GilbertQ,

 

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

 

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

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

 

Delete 1.png

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ,

 

Brilliant!  It works.

 

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

 

 

Hi @liping_qin

 

Glad to assist.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors