A straight line on the histogram, the average value per day by month with bypass filtering

[WaykS]

Hi! There is a table "amocrm_visits" and "date_table"

"amocrm_visits" looks something like this:

id	created_at	price
1	12.06.2023	1200
2	12.06.2023	3000
3	13.06.2023	4200
4	13.06.2023	2400

"date_table" is just a table with dates:

date

I have a "Revenue" measure that counts like this:
Revenue = SUM(amocrm_visits[price])
And there is a graph that I build by day (date_table[date].[Day]), which shows the dynamics of revenue by day
Power BI | Straight line on the histogram, average value per day by month with bypass filtering by calendar

I need to somehow calculate the average revenue by days in a month so that it is displayed as a straight line passing through these columns. I tried to think like this:
AVERAGE(amocrm_visits[amocrm_partners leads.price])
But it is logical that this did not work, since she considers the average value in the context of the day and simply repeats the columns on the chart

Then, I tried to exclude the date using ALL, it really worked and displayed a straight line to me, but not as it should, since the average value was calculated in general across the entire table, and I need it to be the average for the selected month:
CALCULATE(AVERAGE(amocrm_visits[amocrm_partners leads.price]), ALL(date_table))
I will be grateful for your help, I've been working with this all day

[CoreyP]

I was able to get this to work:

Month Average =

VAR _revenue = SUM( SalesTable[Revenue] )

VAR _monthlysales = CALCULATE( SUM( SalesTable[Revenue] ), ALLSELECTED( Date2[Date] ))

VAR _daysinmonth = CALCULATE( COUNT( Date2[Date] ) , ALLSELECTED( Date2[Date] ))

VAR _monavg = DIVIDE( _monthlysales , _daysinmonth , BLANK() )

RETURN

_monavg

The only caveat is that you need both date and month fields in the x-axis, which forces categorical axis, which is kind of lame. I'm sure there's a much better way to achieve this. 

[CoreyP]

If I understand you correctly, you have a bar chart showing revenue at the granularity of date, or day, for some time period--a few months, let's say. And you want to have a line added to the chart which represents the daily average revenue for the specific month. So, if you had Jan, Feb, and Mar data being shown on your bar chart, you have a bar for each day in that time period, ( ~90 days, bars ). You want to see a horizontal line at the level of the average for january, and the line remains horizontal but steps up or down in the subsequent months depending on the change of the average value for that month? Please confirm.

[lbendlin]

Sounds like you want to read about the LINESTX function.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...