Forum Discussion

BaldAccountant's avatar
BaldAccountant
Icon for Helper III rankHelper III
3 years ago
Solved

Power Pivot Identify Month with Maximum Amount

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=0mg4i5l7ytatj31e7emjadfpf

 

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's avatar
    tamerj1
    3 years ago

    Hi BaldAccountant 

    please try

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

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      BaldAccountant
      Icon for Helper III rankHelper III
      3 years ago

       

      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=0mg4i5l7ytatj31e7emjadfpf 

       

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi BaldAccountant 

    please try

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

    • BaldAccountant's avatar
      BaldAccountant
      Icon for Helper III rankHelper III
      3 years ago

      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.