Maximun value of a measure

[Anonymous]

Hello! 

I am trying to calculate the maximum variable cost (Cost/Production)  of a set of assets using MAXX, SUMMARIZETABLE and ADDCOLUMNS. I am finding it impossible to get it right for the aggregates. 

My data model is composed by two main tables (Cost and Production) + two master table (Structure and year).

I have defined OPEX_Prod as a measure for variable cost (SUM(Cost Amount)/SUM(ASSET Production)) and now need to calculate the maximum value of OPEX_Prod for each asset and the set of them. For this purpose I have implemented the measure: 

OPEX_Prod_Max:=MAXX(

ADDCOLUMNS(

ADDCOLUMNS(

SUMMARIZE(Cost;[Asset];[Year];"Amount";sum([Amount]));

"Prod";sum(Production[Production])

);"Amount_Prod";[Amount]/[Prod]);

[Amount_Prod])

This will work for the asset level; but will fail to identify the maximun OPEX_Prod for their aggregates. Any idea how to fix this would be highly appreciated!  

(Note how Asset 1 2020 OPEX_Prod_Max is 6.33 whereas for the Asset 1 aggregate it is 2.25 when it should be 6.33). 

[Tutu_in_YYC · Accepted solution]

Hi Jrg,

Try this?

OPEX_Prod_Max = 

MAXX(
  'Year',
  [Opex_Prod]
)

The summarize function that you have used, some parameters have been deprecated. It is not advisable to use it.

https://dax.guide/summarize/

[Whitewater100]

Hi:

Are you able to try

  Fix Totals = SUMX(VALUES(Structure[Asset],[OPEX_Prod_Max]))

Where I have Structure[Asset] I am trying to use the table and column name of your first column on your visual.

If this doesn't help I know Greg Dickerson (first name on this forum) has measures named Triple Threat Rock & Roll that may be helpful for you.

[Anonymous]

Hi Whitewater! 

Thanks for the reply!

Using: 

SUMX(VALUES('Structure'[Asset];[OPEX_Prod_Max]))

an error will appear about too many arguments being fed into VALUES function. It seems only a column can be fed into it. 

I will check  Greg Dickerson; thanks for the tip! 

[Tutu_in_YYC · Accepted solution]

Hi Jrg,

Try this?

OPEX_Prod_Max = 

MAXX(
  'Year',
  [Opex_Prod]
)

The summarize function that you have used, some parameters have been deprecated. It is not advisable to use it.

https://dax.guide/summarize/

[Anonymous]

Thanks for the help @Tutu_in_YYC ! It partially worked!!! This does the trick for the year but not if I try to filter by Asset Group. (Note Fix_Variable)

Do you know how could I make it extensible to the other table? 

Thanks! 

Javier 

[Tutu_in_YYC · Accepted solution]

Hi Javier,

So what you need to do is replace the Year table with a table that has the complete combination of:

Group, Asset and Year

We can create that using CROSSJOIN

OPEX_Prod_Max_2 = 

MAXX(
  CROSSJOIN('Structure','Year'),
  [Opex_Prod]
)

And you can crossjoin more tables if you have more levels in your matrix visual.

Let me know how it goes!

[Anonymous]

It worked!!! KUDOS! 

Thanks very much @Tutu_in_YYC

[Tutu_in_YYC]

Glad to hear!