Total average line on line chart

[CloudMonkey]

Hi,

I have a line chart showing trends by state. Please can you tell me how to draw a national average line (it should undulate each week, not just be a straight horizontal line)

Thanks!

CM

[v-lili6-msft]

HI, @CloudMonkey 

You could use “all” functions in DAX to create a national average measure:

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

If not your case, please share some simple sample data and expected output.

Best Regards,
Lin

[CloudMonkey]

Thanks, but woudn't this require a second measure on the y axis? I thought only 1 measure could be put on the y-axis?

[v-lili6-msft]

hi, @CloudMonkey 

Do you mean that there is a field in legend value, if so, it could not add a measure again.

and you need to use the Analytics pane in Power BI Desktop.

https://docs.microsoft.com/en-us/power-bi/desktop-analytics-pane

Best Regards,

Lin

[CloudMonkey]

HI Lin,

Analytics pane can create a straight horizontal (average) or angled (trend) line but it can't create a rolling average? The below screenshot shows a horizontal line from the analytics pane and a moving average that I've drawn on in thick black dashed line that I'm aiming for. Does that make sense? (Yes I have a measure in the legend field well)

Thanks,

CM

[v-lili6-msft · Accepted solution]

hi, @CloudMonkey 

I'm afraid it couldn't achieve in Power BI for now.

If there is one field in lenged value, it couldn't add two measure into Values.

As a workaround, you could add a new line chart and set the same x-axis and the national average measure.

Then keep two measure X-axis and Y-axis coincident.

Best Regards,
Lin

[CloudMonkey]

Many thanks Lin, that workaround works for the time being.

[v-lili6-msft]

hi, @CloudMonkey 

You are welcome. 

could you please mark the helpful replies as Answered?

Best Regards,

Lin