Power Pivot Identify Month with Maximum Amount

[BaldAccountant]

I am working in Power Pivot.

I have made a sample file

https://www.dropbox.com/scl/fi/5z9t0adnmw4pbbbo3czz4/Test-Max-Name.xlsx?dl=0&rlkey=0mg4i5l7ytatj31e7...

I made a measure to calculate the max monthly value for various calculations - total cases, net profit per case, etc.

The user can choose which calculation they want the max value for.

But what I want to do is also identify the name of the month with the maximum value.

Thanks in advance for any help you can provide.

For example if you choose cases from the drop down in cell b18, the max of 182 shows in cell c20.  I can look at the pivot table and see that is the amount for the month of Oct, but I want a formula to tell which month has the max value for that calculation

[tamerj1 · Accepted solution]

Hi @BaldAccountant 

please try

Max Month :=
MAXX (
TOPN ( 1, VALUES ( Data[Date (Month)] ), CALCULATE ( COUNT ( Data[Case] ) ) ),
Data[Date (Month)]
)

[BaldAccountant]

@tamerj1 That worked, but just in case anyone else has this question and is looking at this thread, your solution was just looking at cases, not the dynamic measure of Analysis Measure, so the only thing you could calculate the max month for was cases.  I substituted that measure for the count(data[case]) and it worked fine.  Thank you very much again.

[FreemanZ]

hi @BaldAccountant 

Unable to access your file, supposing Cases is a measure, try like:

MaxMonth =

VAR _maxcase =

MAXX(

    ALL(TableName[Month]),

    [Cases]

)

RETURN

FILTER(

    VALUES(TableName[Month]),

    [Cases] = _maxcase

)

[BaldAccountant]

I am getting an error - a table of multiple values was supplied where a single value was expected.  

I am sorry the link to dropbox is not working.  It says I cab share it with anyone who has the link...

If it helps this is the measure that I used to get the max amount

Max:=var totalmonth =SUMMARIZE(Data,Data[Date (Month)],"max1",CALCULATE(Analysis[Analysis Measure]))

return maxx(totalmonth,[max1])

and here is the analysis measure - I am sure there is probably a better way to do this, but I am relatively new to dax

Analysis Measure:=SWITCH([Selected Analysis],"Cases",count(Data[Case]),"Charges",sum(Data[Charge]),"Cost",sum(Data[Cost]),"Charges per Case",DIVIDE(sum(Data[Charge]),count(Data[Case]),0),"Cost per Case",DIVIDE(sum(Data[Cost]),COUNT(Data[Case]),0),"Net Profit",sum(Data[Charge])-sum(Data[Cost]),"Net Profit per Case",DIVIDE(sum(Data[Charge])-sum(Data[Cost]),count(Data[Case]),0),"Minutes",sum(Data[Time]),"Net Profit per Hour",divide(sum(Data[Charge])-sum(Data[Cost]),sum(Data[Time]),0)*60,0)

The idea is the user can choose what type of calculations they want to see using the analysis measure.

Here is another link in case the previous one was just bad

https://www.dropbox.com/scl/fi/5z9t0adnmw4pbbbo3czz4/Test-Max-Name.xlsx?dl=0&rlkey=0mg4i5l7ytatj31e7...