Issue with MAXX on a hierarchy

[masplin]

I'm trying to create a calculation that works out the maximum of new COVID cases by both us state and region  as of the previous day in order work out if a new all time high has been reached

So far i have this measure where Maxday is the last day of data. [7 Day Average Caaes] is just a rolling average.

Max 7D Av Yesterday = MAXX(FILTER(
	                SUMMARIZE('us-states',
	                                           [state],
	                                           DateTable[Day]
	                                           ),
	                           DateTable[Day]<[MaxDay]
	                           )      ,          
                                           'us-states'[7 Day Average Cases]
                                           )

This works fine for each state but if I put it in a matrix Region/State the Region is completely wrong. This calcaution works out the maximum ofr each state, which could be on different days, and then just sums it to give the region number.  This is is not the maximum the region was on any one day.

This is a link to the input data by state for just a single Region state data .

Can someone explain how to get the total row to be the maximum of the total region data on a single date not the sum of the maximums of each state, but each state to be it's own maximum 9not the value on the day the region was at a maximum)? 

Appreciate any assistance

Mike

[v-easonf-msft]

Hi， @masplin 

Can you show your expected results in the form of a table?

It will be more convenient for us to solve your problem.

Best Regards,
Community Support Team _ Eason

[masplin]

Sorry seem to have accidentally delete the table of data

State Data 

In row 45 column g the max of up to the previous day is say 387 for alabama on 24th, but is 969 for Florida on 17/4.  The maximum of rthe whole state is 4409 achieved on 24/5.  My current calcaution finds the maximum for each state and then the region calcaution is 4,990 instead of 4,409 as adds these maximums together, even though they are on different dates.

So what I am after is a maximum for both states and region i.e. 4,409 for grand total.

Thanks for any help