Daily average for a full month

[andwele]

I'm stuck I've tried searching for the solution to this in multiple ways and if the solution is obvious I appologize. 

I have data from multiple sites with vendors different vendors picking up tons from each site. I would like to create a measure that gets the daily average on a full month basis for each vendor. I have a seperate Date table created using CALENDARAUTO with year, month, month number, quarter, year month number, and year month. 

I'm able to get the average based on the distinct count using these two measures:

Total Tons = SUMX('INBOUND (2)', 'INBOUND (2)'[TONS])

Average Tons Per Day  = AVERAGEX( VALUES(DATES[Date]), [Total Tons])

Here is my source data 'INBOUND (2)':

DATE	Vendor	TICKET	SITE_TICKET	TONS	SITEID
9/6/2020 0:00	Vendor1	1303298	1303298	26.57	Site1
8/26/2020 0:00	Vendor1	1303185	1303185	24.11	Site1
9/11/2020 0:00	Vendor4	6051733	6051733	23.96	Site5
9/5/2020 0:00	Vendor1	1303290	1303290	25.41	Site1
9/11/2020 0:00	Vendor1	1303350	1303350	24.34	Site1
8/18/2020 0:00	Vendor1	1303104	1303104	26.15	Site1
8/20/2020 0:00	Vendor1	6051494	6051494	24.69	Site5
8/26/2020 0:00	Vendor1	6051558	6051558	24.53	Site5
8/20/2020 0:00	Vendor1	1303123	1303123	26.19	Site1
9/7/2020 0:00	Vendor1	1303307	1303307	24.15	Site1
9/10/2020 0:00	Vendor1	1303333	1303333	25.54	Site1
8/28/2020 0:00	Vendor1	1303207	1303207	24.77	Site1
9/2/2020 0:00	Vendor3	2345562	161	23.6	Site3
8/22/2020 0:00	Vendor1	1303145	1303145	25.63	Site1
8/26/2020 0:00	Vendor2	7044875	7044875	24.99	Site4
8/16/2020 0:00	Vendor1	6051458	6051458	30.01	Site5
8/21/2020 0:00	Vendor1	6051504	6051504	24.73	Site5
8/21/2020 0:00	Vendor2	7044832	7044832	23.98	Site4
8/19/2020 0:00	Vendor1	6051487	6051487	30.36	Site5
8/20/2020 0:00	Vendor1	6051495	6051495	30.08	Site5
8/17/2020 0:00	Vendor2	5139851	5139848	24.03	Site2
8/24/2020 0:00	Vendor1	6051534	6051534	30.4	Site5
8/17/2020 0:00	Vendor1	6051465	6051465	30.13	Site5
9/2/2020 0:00	Vendor1	1303257	1303257	24.55	Site1
9/7/2020 0:00	Vendor1	1303308	1303308	26.48	Site1
9/7/2020 0:00	Vendor3	7044977	7044977	24.05	Site4
9/6/2020 0:00	Vendor2	5139945	5139942	24.81	Site2
9/11/2020 0:00	Vendor2	5139970	5139967	22.97	Site2
8/22/2020 0:00	Vendor1	1303146	1303146	24.56	Site1
8/25/2020 0:00	Vendor1	6051546	6051546	30.14	Site5
8/26/2020 0:00	Vendor1	6051559	6051559	30.44	Site5
8/16/2020 0:00	Vendor1	1303085	1303085	25.13	Site1
8/23/2020 0:00	Vendor2	5139872	5139869	23.77	Site2
8/27/2020 0:00	Vendor1	6051569	6051569	30.18	Site5
8/28/2020 0:00	Vendor1	6051583	6051583	30.02	Site5

[amitchandak]

@andwele , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Try if this can work

Average Tons Per Day = calculate(AVERAGEX( VALUES(DATES[Date]), [Total Tons]) , allexcept(Date, Date[Month-year]))

[lbendlin]

So many questions 🙂

 what is a full month? with or without weekends and holidays? Where are these sites located? What are the weekend and holiday rules at these sites?  Do you only want to sed the daily average for completed months or do you also want to show the Month To Date average for the current month?

Here's your sample data.  Neither August nor September are complete. Are you looking more for a sliding window average maybe?

[andwele]

Sorry I didn't have a chance to get back to this forum there are so many competing issues with my job and went into a total crisis mangement with work pretty much right after I posted this. Anyway thank you for view my post. to answer your questions while my explanation was lacking what I want is pretty straight forward. I want to calculate the average based on all the days in the month that have occured. so if I'm looking at data today 10/21/2020 I would like the month average to be the total tons divided by 21 days. if I look at september 2020 I would like to see the month average to be the total tons divided by 30. The vendors are servicing the sites as they are called including on weekends and holidays so there is no distinction there but they are not called on all days. So Power BI understandably divides the total by the total number of days they provided service I just don't know how to not do that.

[lbendlin]

Create a calculated column in your calendar table that computes the number of days in that date's month.

Use that column value as the denominator.