How to obtain the max value and its capacity

[o59393]

hi all

I am trying to obtain in a measure the capacity value where its demand is the max.

In the following example, the value that should return the measure should be the one from december (857,793) since the max demand is in december.

How can I obtain the value for the capacity meeting the above condition?

I already have the demand:

Max demand = MAXX(VALUES('Master Table Month'[Month]),[% of sales by line what if rolling])

https://1drv.ms/u/s!ApgeWwGTKtFdhyEaO7IZgUhf2Q-l?e=7mruzM

Thanks all.

[amitchandak]

@o59393 , Try like

Max =
var _max =calculate(maxx(values(Table[Month]),[demand UC]),allexcept(Table[Month]))
return
calculate([demand UC], filter(Table, Table[Month] =_max))

[o59393]

Hi @amitchandak 

We are getting closer, I guess I understand the logic.

the var _max is for returning the month whose demand has a max. In this case it should retain "december"

The return should calculate the capacity (in this case it's called "capacity measure") when the month matches the var max: december for this example.

I tried this:

Max = 
var _max =calculate(maxx(values('Append1 Balance your lines'[Month]),[% of sales by line what if rolling]))
return
calculate([Capacity Measure], filter('Append1 Balance your lines', 'Append1 Balance your lines'[Month] =_max))

I am getting an empty result. Can you please advise how to fix it?

thanks!

[v-yiruan-msft · Accepted solution]

Hi @o59393 ,

You can create a measure as below:

_maxCapacity =
VAR _maxMonth =
    CALCULATE (
        MAX ( 'Append1 % of total country demand produced in line'[Month] ),
        FILTER (
            ALL ( 'Append1 % of total country demand produced in line' ),
            [% of sales by line what if rolling] = [Max demand]
        )
    )
RETURN
    CALCULATE (
        [Capacity Measure],
        FILTER (
            ALL ( 'Append1 Balance your lines'[Append1 % of total country demand produced in line.Month] ),
            'Append1 Balance your lines'[Append1 % of total country demand produced in line.Month] = _maxMonth
        )
    )

Best Regards

Rena

[o59393]

@v-yiruan-msft  Awesome!

Thanks 🙂