Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Coldsmokeski
Frequent Visitor

MAX value of compared measures

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!

1 ACCEPTED SOLUTION
lc_finance
Solution Sage
Solution Sage

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

View solution in original post

3 REPLIES 3
ChrisMendoza
Resident Rockstar
Resident Rockstar

@Coldsmokeski -

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.

1.PNG






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



lc_finance
Solution Sage
Solution Sage

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.