Forum Discussion
Power Pivot Identify Month with Maximum Amount
- 3 years ago
please try
Max Month :=
MAXX (
TOPN ( 1, VALUES ( Data[Date (Month)] ), CALCULATE ( COUNT ( Data[Case] ) ) ),
Data[Date (Month)]
)
- BaldAccountant3 years ago
Helper III
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