Getting a MAX of summarised data using DAX

[Anonymous]

Hi all, I am developing a reporting / forecasting solution that will run in Excel rather than Power BI. The reasons being that the end-users work regularly in Excel. The wider solution will be based on SAP report, exported to separate Excel files and my solution will connect to these via Power Pivot NOT Power Query!  

One of the tables, which I have over-simplified and called Zoo, is like this:

Animal	Quantity	House	Room	PostDate
Katz	198	Main	Kitchen	22.03.2023
Fish	211	Out	Pool	22.03.2023
Dogz	234	Main	Dining	22.03.2023
Katz	200	Main	Dining	23.03.2023
Dogz	50	Main	Kitchen	23.03.2023
Ape	60	Main	Dining	23.03.2023
Horz	20	Main	Kitchen	23.03.2023
Fish	143	Out	Pool	23.03.2023
Horz	466	Main	Dining	24.03.2023

I use the detail for various measures but I also need summary measures. Example: How much was consumed per House per day?

TotalPerHousePerDay =CALCULATE( SUM('Zoo'[Quantity]) ; ALLEXCEPT('Zoo'; 'Zoo'[Plant];'Zoo'[PostDate]) )

And in a Pivot this returns:

House	Date	Total
Main	22.03.2023	432
Out	22.03.2023	211
Main	23.03.2023	330
Out	23.03.2023	143
Main	24.03.2023	466

Great, but now I need to know the Maximum consumed per House (over e.g., the last N days) and I just cannot make this work! What I want returned is …

Out	211
Main	466

What I get back is the Max Quantity of each house but at row level and not the max of the sum of the days … if you see what I mean!  Ideas?

\ paul

[johnt75]

Try

Max per day =
MAXX ( SUMMARIZE ( 'Zoo', 'Zoo'[House], 'Zoo'[Date] ), [Total] )

[Anonymous]

Thanks but so far, not working 😟. What I do not understand is, what the [Total] is or does because in Zoo I do not have a Total column just a Quantity per Row. What am I missing?

[johnt75]

[Total] should be the measure you already have which calculates the total per house per day.