Forum Discussion

asjones's avatar
asjones
Helper V
1 year ago
Solved

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

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....
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
asjones
Helper V
1 year ago

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.