cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Power Pivot Identify Month with Maximum Amount

I am working in Power Pivot.

I have made a sample file

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.

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

1 ACCEPTED SOLUTION
Super User

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

4 REPLIES 4
Super User

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

@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.

Super User
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
)

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.