Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Howdy
I'm new to the BI game and hoping for some assistance.
I have a dataset from which I have a measure that calculates the sum of the 'amount' field for each month of a year.
I'm trying to locate the appropriate formula to use to compare the totals of three of these measures and return the MAX value.
I have this as current measures:
Reference Measure: Sum of AMOUNT:=SUM([AMOUNT])
Measure1: Oct'19_Total = CALCULATE([Sum of AMOUNT],'Facts'[MM-YYYY]="10-2019")
Measure2: Nov'19_Total = CALCULATE([Sum of AMOUNT],'Facts'[MM-YYYY]="11-2019")
Measure3: Dec'19_Total = CALCULATE([Sum of AMOUNT],'Facts'[MM-YYYY]="12-2019")
I was hoping this would work but no luck:
New Measure: FY19_Q1_Max = MAX([Oct'19_Total],[Nov'19_Total],[Dec'19_Total])
Any ideas?
Thanks!
Solved! Go to Solution.
Hi @Coldsmokeski ,
what is the error when you enter the formula?
The MAX formula only accepts 2 parameters (https://docs.microsoft.com/en-us/dax/max-function-dax) so you'd need to have 2 MAX formulas:
New Measure: FY19_Q1_Max = MAX(MAX([Oct'19_Total],[Nov'19_Total]),[Dec'19_Total])
Regards,
LC
Using https://community.powerbi.com/t5/Desktop/Calculting-the-Maximum-of-a-calculated-sum-depending-on-fil... as an example:
FY19_Q1_Max = CALCULATE ( MAXX ( SUMMARIZE ( Facts, Facts[MM-YYYY], "temp", SUM ( Facts[Amount] ) ), [temp] ), Facts[MM-YYYY] = "10-2019" || Facts[MM-YYYY] = "11-2019" || Facts[MM-YYYY] = "12-2019" )
Seems to work in my small sample.
Proud to be a Super User!
Hi @Coldsmokeski ,
what is the error when you enter the formula?
The MAX formula only accepts 2 parameters (https://docs.microsoft.com/en-us/dax/max-function-dax) so you'd need to have 2 MAX formulas:
New Measure: FY19_Q1_Max = MAX(MAX([Oct'19_Total],[Nov'19_Total]),[Dec'19_Total])
Regards,
LC
Thanks @lc_finance , that worked! Clever solution.
The error I received was 'Too many arguments were passed to the MAX function. The maximum argument count for that function is 2.'
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |