DAX: Calculate MAX of a measure in a subgroup

[bidevsugmen]

Hello Everyone,

I'm facing the issue as mentioned below-

Sample data:

I am facing difficulty in developing the logic for "occupancy as per bottleneck reactor" as a calculated measure in Power BI.

Using MAXX(Table, Table[Total Hr Occupancy]) gives me same values as in the column Total Hr Occupancy, instead of picking up the respective max values.

What if parameters - Stage ratio and Batch size

Kg/Hr = [Stage ratio]*[Batch size]*[Eq. ratio] / [Std time per batch]     ([Eq. ratio]  and [Std time per batch] are input fields not mentioned in the sample data)

Kg = input field

[Total Hr Occupancy] = Kg / [Kg/Hr]

Any help would be appreciated.

Thanks in advance,

Suguna.

[v-shex-msft]

HI @bidevsugmen,

Can you please share a pbix file wiht some sample data and expected result to help us clarify your requirement? It will be help for us to test and coding formula.

Regards,

Xiaoxin Sheng

[bidevsugmen]

Hi @v-shex-msft

You can download the pbix file from here. It also has the Excel sheet which is the data source and a notepad file which consists of the formulae that we ahev already tried.

The column header highlighted in purple in table Product wise parameter is the point of discussion here.

Thanks,

Suguna.

[v-shex-msft · Accepted solution]

Hi @bidevsugmen,

Maybe you can try to use following formula, this measure will change by what if parameter:

Kg/Hr = MAX('U-3'[Stage ratio])*[Batch Size Value]*MAX('U-3'[Eq. API ratio]) / MAX('U-3'[Std. Time/batch (hrs)])

Total Hr Occupancy = [Batch Size Value]/[Kg/Hr]

Regads,

Xiaoxin Sheng

[Anonymous]

Try this

measure =
MAXX ( VALUES ( Table[Grouping key] ), Table[Total Hr Occupancy] )

you could replace the table[total hr occupancy] with an equivialnt measure, which might be better as calculated columns wont respond to changes in what if parameters in the same way as measures do.

[bidevsugmen]

Hi @Anonymous

I tried implementing your suggestion but did not get expected result. It produces the same values as Total Hrs Occupancy.

Thanks,

Suguna.