Create a measure to select the maximum value of another measure

[Anonymous]

Hello,

I have a problem that I hope someone can help me to solve.

I have a table showing 'cost per item' per month. The 'cost per item' is a measure which calculates a value based on several measures and columns.

What I want is to create a new measure ('Max value') which selects the highest value from the 'cost per item'-measure, so that my table will look something like this:

I have tried using the MAX-function, but I encounter problems since this is not a column but a measure.

All help will be appreciated!

[Jon_vB]

If you want to get the Max of a Measure in order to set the Y axis Max value - you can now use Visual Calculations.  With a measure named "VisitsPerDay" - you could add a Visual Calculation:

Max Y value = Calculate(Max(VisitsPerDay), Removefilters()) * 1.1

 (the * 1.1 will make the max value 10% higher ... you may want to play with that factor a little to get it looking how you wan.

[Prahlad_j]

Hii, what if I have multiple column on which value of measure depends ,then how we can do this and those column we are having in Matrix as well but we want maximum value with respect to the first column.

Eg: we have hospital,serviceline and drug code as columns in Matrix. We want the value of maxcost peritem (considering hospital,serviceline and drug code) and the maximum value of maxcostperitem irrespect of any hospital. I want to include the hospital while calculating the maxcostperitem but exclude it while calculating maximum value of maxcostperitem

Any help on this will be appreciated.

[Anonymous · Accepted solution]

Hello,

Hello, @TheoC  and @Anonymous  - I apprecciate your effort in trying to help me on this!

I tried to solve it in the ways you suggested, but I did not succeed - not sure why. Probably there is something in my dataset that comlicates this.

But I found a way to solve it by a workaround: I created a new calculated table showing months instead of dates, and then added my CostPerItem-measure as a column in this new table. Then I created a new measure using the max-function and was able to get the highest value . By doing this I was able to use the column in the function, and not struggle with using a measure.

Probably not the best way to do it, but it seems to work out.

Example of the new table:

Month	CostPerItem
January	2812
February	2188
March	2264
April	1982
May	1949
June	1579
July	2155
August	1926
September	2588
October
November
December

New measure:

MaxCost = CALCULATE(SUM(CostPerItem);FILTER(NewTable;CostPerItem=MAX(CostPerItem)))

[Anonymous]

Hi @Anonymous ,

It's glad to hear that your problem has been resolved. And thanks for sharing your solution here. Could you please mark your post as Answered? It will help the others in the community find the solution easily if they face the same problem with yours. Thank you.

Best Regards

[TheoC]

Hi @Anonymous 

Give this a go as a Calculated Column:

MaxCostPerItem =

MAXX (

KEEPFILTERS ( VALUES ( 'Table (2)'[Cost per item (measure)] ) ) ,

CALCULATE ( MAXX ( 'Table (2)','Table (2)'[Cost per item (measure)] ) )

)

Hope it helps 🙂

[Anonymous]

Thank you for your reply, @TheoC .

I get an error when trying your solution - because my "Cost per item (measure)" is a measure and not a column, I am not able to select it after the VALUES-expression, as this expression wants a table or column reference.

MaxCostPerItem =

MAXX (

KEEPFILTERS ( VALUES ( 'Table (2)'[Cost per item (measure)] ) ) ,

CALCULATE ( MAXX ( 'Table (2)','Table (2)'[Cost per item (measure)] ) )

)

[TheoC]

@Anonymous, hope the below is what you're after?

[Anonymous]

Hello,  @TheoC 
 
Thank you for your help, I really appreciate it.
I think we're close to something here. I used your formula, and it gave me this.

Formula: MaxValue = Calculate(MAXX(Table_1, [CostPerItem]), ALL(Table_1)

It may seem like it summarize more than it should, or there's a trouble with some filters or something. I will continue to look into it.
 My Month-column is retrieved from a date table.
In my file I use a lot of different measures using values from different tables, which may complicate this a bit.
 
This is the measure for the CostPerItem:
CostPerItem = DIVIDE([Sum_cost_total];[calculated_production])
 
This measure further consists of these two measures:
[Sum_cost_total] = CALCULATE(SUM(General_ledger[amount]);General_ledger[Account]<>"9999")
[calculated_production] = (SUM(rawMaterial_1) + SUM(rawMaterial_2)+Sum(rawMaterial:3)
 

[Anonymous]

Hi @Anonymous ,

Please update the formula of measure [MaxValue] as below and check whether you can get the correct result:

MaxValue = MAXX(ALL('General_ledger'[Month]),[CostPerItem])

Best Regards

[TheoC]

@Anonymous, link to the PBIX here 🙂

I'm not sure why that is happening to yours... I am limited to the screenshot unfortunately. Hopefully my PBIX will help resolve things 🙂

[Anonymous]

Thank you, I will look into it!

[TheoC]

@Anonymous  How did it go?