Average Reference / Trend Line - for Multipole LInes on a Chart

[asjones]

I have a line chart chart with expenses by month (x-axis) and each line represents a fiscal year (see screenshot below). 

I would like a trend line showing spending by month (average) all the years. I learned that a line chart by default does not support trend lines for multiple lines. I thought a "Reference" line might really be what I was after, but relaized a referene line is a single straigth line and does not go up/down across the chart based on what is displayed.

What I am after may not reallyl be a "trend line", but wondering how do I display that and handle that.

Note: The number of years being displayed may vary based on what is selected in a slicer. The group of who's spendngin in the chart is also something that would be selected in a slicer.

Guessing there is some DAX that I need, just hoping it is not too complicated.

Thanks for your help.

Alan

[Anonymous · Accepted solution]

Hi @asjones ,

Thanks for reaching out.

Here's my sample with dummy data based on your post. Below is the common way to show the sales.

As you can see above, the lines show the sum of sales for Tom and Alex. There's also a slicer for names filtering.

Now if you want the average lines in the chart, you need to add each lines one by one with measures.

average of sales by alex = CALCULATE(AVERAGE('Table'[Sales]),FILTER('Table',[Name]="Alex"))

average of sales by tom = CALCULATE(AVERAGE('Table'[Sales]),FILTER('Table',[Name]="Tom"))

average of sales by alex = CALCULATE(SUM('Table'[Sales]),'Table'[Name]="Alex")

average of sales by tom = CALCULATE(SUM('Table'[Sales]),'Table'[Name]="Tom")

The slicer still works, if you select Alex, the line chart will show the lines only about Alex.

In summary, the common way to show different lines in the line chart is to remove the Legend and create measures to show each of them one by one.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[asjones]

@lbendlin 

Thanks for the response. I was trying to figure out what i really need to do related to "average" as I have one big table going current year back 7 years of data. I am just doing a simple SUM() for current lines. Would I do an AVERGE also and it would work cleanly while still working for the multi year lines? To get the multi year lines right (based on examples) I had to dirll down in my date hierachy.

On the Month numbering I read a bunch of different articles and tried sort by thing etc and did not get anywhere. So I gave up and faked it.  One of the errors was "Sort by another column.. We can't sort the 'Month Name' column by 'Period-Month-Year' for thesame value in 'Month Name'. Plese choose a different colum for sorting or update the 'Period-Month-Year'" Ths error message was just confusing.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

Yes, you would add average data points for each month and plot that as a separate line.

By the way no need to fudge the month names like this. Use custom sorts.