Add average line and limit aggregate to today

Question

Hello,I currently have this graph&nbsp;it shows a value per day for the selected months in the bars. The aggregate for said values on the lines. And I have some buttons you can click to switch the value you are showing.What I'd like is a way to add an average line for each selected month. So on top of having an aggregate line I'd also have an average line that's horizontal.And also for a way to have the aggregate line stop on today. So I don't show that horizontal lineafter the last date as it is quite confusing.Thanks in advanceFORMULASI'm Adding the formulas I use in case they are helpful to understand the situationAggregate formula for november.Aggregate Nov = SWITCH(SELECTEDVALUE('Button Table'[Column1]),1,CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;count(Filteredytt_servicerequestheader[ytt_name]),&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED(Filteredytt_servicerequestheader[Expected delivery date].[Day]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISONORAFTER(Filteredytt_servicerequestheader[Expected delivery date].[Day], MAX(Filteredytt_servicerequestheader[Expected delivery date].[Day]), DESC)),MONTH(Filteredytt_servicerequestheader[Expected delivery date])=11),2,CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;SUM('Item Merge'[Tons]),&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED(Filteredytt_servicerequestheader[Expected delivery date].[Day]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISONORAFTER(Filteredytt_servicerequestheader[Expected delivery date].[Day], MAX(Filteredytt_servicerequestheader[Expected delivery date].[Day]), DESC)),MONTH(Filteredytt_servicerequestheader[Expected delivery date])=11),3,CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;SUM(Filteredytt_servicerequestheader[ytt_estimatedtruckno]),&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED(Filteredytt_servicerequestheader[Expected delivery date].[Day]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISONORAFTER(Filteredytt_servicerequestheader[Expected delivery date].[Day], MAX(Filteredytt_servicerequestheader[Expected delivery date].[Day]), DESC)),MONTH(Filteredytt_servicerequestheader[Expected delivery date])=11),4,CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;count(Filteredytt_servicerequestheader[ytt_to]),&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED(Filteredytt_servicerequestheader[Expected delivery date].[Day]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISONORAFTER(Filteredytt_servicerequestheader[Expected delivery date].[Day], MAX(Filteredytt_servicerequestheader[Expected delivery date].[Day]), DESC)),MONTH(Filteredytt_servicerequestheader[Expected delivery date])=11),CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;count(Filteredytt_servicerequestheader[ytt_name]),&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED(Filteredytt_servicerequestheader[Expected delivery date].[Day]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISONORAFTER(Filteredytt_servicerequestheader[Expected delivery date].[Day], MAX(Filteredytt_servicerequestheader[Expected delivery date].[Day]), DESC)),MONTH(Filteredytt_servicerequestheader[Expected delivery date])=11))Bars FormulaSelected Calculation = SWITCH(SELECTEDVALUE('Button Table'[Column1]),1,COUNT(Filteredytt_servicerequestheader[ytt_name]),2,SUM('Item Merge'[Tons]),3,Sum(Filteredytt_servicerequestheader[ytt_estimatedtruckno]),4,COUNT(Filteredytt_servicerequestheader[ytt_to]),COUNT(Filteredytt_servicerequestheader[ytt_name]))

v-xiaotang · Answer

Hi&nbsp;SuchCT&nbsp;
Thanks for reaching out to us.
&nbsp;
For the first question, it seems not possible to get average line by filtering the visual itself, but you can add a slicer, here's an example for your reference,
https://community.powerbi.com/t5/Desktop/How-can-I-filter-the-color-of-my-bar-from-my-bar-chart-according/td-p/2119273
For the second question, could you share a sample file? so that we can work on it. Thanks.
&nbsp;
&nbsp;
Best Regards,
Community Support Team _Tang
If this post&nbsp;helps, please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Add average line and limit aggregate to today

1 Reply

Related content

Average monthly sales buy aggregated

Average Aggregated Data

Copilot, limitations?

Monthly averages - Calculated column - Aggregation

Data limit Issues