cancel
Showing results for
Did you mean:
mlleverino Regular Visitor

## using MAX to identify the max value calculated by a measure

I'm trying to use the MAX funtion to reference a measure, but getting an error stating that MAX only references a column.  what is the best way to get around this to where it will allow me to reference a measure?

9 REPLIES 9
Ross73312 Super Contributor

## Re: using MAX to identify the max value calculated by a measure

Measures only come up with a single result, which is why the Max having trouble. What Max wants to do is scan through a list of values and give you the highest.

Instead you need to write a DAX expression that will calculate a set of values, based on your original measure, and then find the max of that set.

Here is something quickly that might work:

```Measure Max = IF(
countrows(values('Table'[GroupingField])) = 1,
[YourMeasure],
MAXX(
values('Table'[GroupingField]),
[YourMeasure]
)
)```

Where grouping field is what thing you want to group your measure results by.  For example, if you wanted to calculate your measure on monthly figures, you'd need a field that denotes the YearMonth.  If it was daily, you could use a single date here.

Proud to be a Datanaut!

mlleverino Regular Visitor

## Re: using MAX to identify the max value calculated by a measure

Could't quite get it to work.  Here's a sample data set.

Revenues (measure)      # of Machines (measure)    Rev/machine (measure)      MAX Rev/machine     Opportunity

Customer A           35,000                         5                                             7,000                           7,000                           0

Customer B           60,000                         15                                           4,000                           7,000                           45,000

Customer C           25,000                         10                                           2,500                           7,000                           45,000

what I'm trying to do is write a DAX equation that will show the MAX (7,000) across all customers which will then allow me to calculate the opportunity across the not-MAX customers (A & B).  The opportunity will be calculated by subtracting the MAX rev/machine from the rev/machine and multiplying that by the number of machines.

Example:  Considering customer B, the opportunity will be 7,000 (MAX rev/machine) - 4,000 (rev/machine) x 15 (machines) = 45,000

Ross73312 Super Contributor

## Re: using MAX to identify the max value calculated by a measure

Ok lets try these two measures:

```Rev/Machine Max = Calculate(
IF(
countrows(values('TableName'[Customer])) = 1,
[Rev/Machine],
MAXX(
values('Table'[Customer]),
[Rev/Machine]
)
),
ALL('TableName')
)```

`Opportunity = [Rev/Machine Max] - [Rev/Machine] * [# of Machines]`

Proud to be a Datanaut!

mlleverino Regular Visitor

## Re: using MAX to identify the max value calculated by a measure

We're getting closer.  the formula was accepted and is calculating something as there is a value noted even though it's not the correct MAX i'm looking for.  If I can figure what is being calculated to reflect the value shown, I think i'll be able to close this out.

Ross73312 Super Contributor

## Re: using MAX to identify the max value calculated by a measure

Just realised i might have made an error with this formula.  Try this instead:

`Opportunity = ([Rev/Machine Max] - [Rev/Machine]) * [# of Machines]`

Order of operations is important lol

Proud to be a Datanaut!

mlleverino Regular Visitor

## Re: using MAX to identify the max value calculated by a measure

Thanks Ross. I did notice that one.
I'm going to play around with the first calculation to see if I can get it to work. I studied the syntax you provided to understand what was being referenced and it's starting to make sense.
mlleverino Regular Visitor

## Re: using MAX to identify the max value calculated by a measure

I started breaking down the expression you provided trying to figure out what part is not working.

TEST1 - =countrows(values('TableName'[Customer])) = 1       WORKED

TEST2 - =calculate(if(countrows(values('TableName'[Customer])) = 1,[rev/maching], "you"))           DID NOT WORK.

If I replace [rev/machine] with other values under quotes, it'll work.  I tried inserting different measures but calculation failed.  Definitely something off when having to reference a measure.

TEST3 - =calculate(if(countrows(values('TableName'[Customer])) = 1, "me","you"))          WORKED

I'll be doing more testing to see if I can get this to work.

Ross73312 Super Contributor

## Re: using MAX to identify the max value calculated by a measure

Thats because you can't use a measure within a calculate statement.  You will need to take all of the code inside that measure, and put it inside your calculate statement.  If your measure references other measures, you'll need to get those bits of code too.  Dax lets you spread out your code (like a programming language) to keep it all manageable.

Proud to be a Datanaut!

mlleverino Regular Visitor

## Re: using MAX to identify the max value calculated by a measure

After reading the Power Pivot and Power BI book by Rob Collie & Avi Singh, I figured this thing out.

1.  =MAXX(VALUES(vw_FST_Revenue_Paid_Per_Production_Tool_Report[Parent Customer]),[RpT PARTS])

This allowed for me to get the MAX revenue per tool across the specific platforms.  The MAX was noted only on the subcategory total.

2.  =MAXX(ALLSELECTED(vw_FST_Revenue_Paid_Per_Production_Tool_Report[Parent Customer]),[RpT PARTS])

This allowed me to filter out certain customers while adjusting the revenue per tool Max based on what i included/excluded.  Similar to "1." above, but this DAX noted the MAX across all rows, not just the subcategory total.

3. I then subtracted those two and multiplied it by the tool counts.  The result was the opportunity.

Announcements #### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given. #### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities. Top Solution Authors
Top Kudoed Authors (Last 30 Days)