Summarizing Daily Data to a Monthly Table of Ratios

[Anonymous]

Hi all,  Thank you for taking the time to look at this. 

I have daily water production data that I am trying to summarize as the monthly  average  of the average day in each month divided by the maximum day in the year.

I used the following measures to create a summary table by month and year

Average Day = AVERAGE(daily_usage[Usage])

Max Day = max(daily_usage[Usage])

Max Day Year = CALCULATE([Max Day],ALLEXCEPT('Calendar','Calendar'[Year]))

ADM/MDY = DIVIDE([Average Day],[Max Day Year])

Which gives me the correct table:

Year	Month	Average Day	Max Day	Avg Day/Max Day
2018	January	216	444	0.49
2018	February	224	444	0.5
2018	March	245	444	0.55
2018	April	292	444	0.66
2018	May	343	444	0.77
2018	June	397	444	0.89
2018	July	407	444	0.92
2018	August	415	444	0.93
2018	September	377	444	0.85
2018	October	313	444	0.7
2018	November	259	444	0.58
2018	December	220	444	0.5
2019	January	209	438	0.48
2019	February	200	438	0.46
2019	March	231	438	0.53
2019	April	280	438	0.64
2019	May	310	438	0.71
2019	June	366	438	0.84
2019	July	405	438	0.92
2019	August	407	438	0.93
2019	September	363	438	0.83
2019	October	307	438	0.7
2019	November	240	438	0.55
2019	December	204	438	0.47

 But when I try to summarize it to the month I always end up with the average use over all periods in that month divided by the max day of all periods.  What I need to get to is just andaverage of the last column:

Month	Avg Day/Max Day
January	0.485
February	0.48
March	0.54
April	0.65
May	0.74
June	0.865
July	0.92
August	0.93
September	0.84
October	0.7
November	0.565
December	0.485

 

Thanks in advance for any help.

Chris

[Anonymous]

Hi @Anonymous ,

Any updates?

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @Anonymous ,

Could you tell me if my post helps you? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.

Best Regards,
Eyelyn Qin

[Anonymous]

I am sorry but once I had a solution I moved on.  I am trying to get some time to try your solution.

[Anonymous]

Hi @Anonymous ,

Sorry I could not clarify your scenario without data sample, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

In case you may try:

=AVERAGEX(FILTER(ALL('TABLE'),[MONTH]=MAX('TABLE'[MONTH])),[Avg Day/Maz Day])

Best Regards,
Eyelyn Qin

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

[Anonymous]

So I have a solution but seems like a terribly inefficient way to accomplish it.  I used summarize to create a table of annual max days.  Then I created a column in my original table and used lookup to link the max day in each year to the daily data.  Finally I created measures and used averagex to calculate the tables.