GROUP BY logic

[bidevsugmen]

Hi,

Consider the sample data below.

FG - Finished Good

RM - Raw Material

I want to calculate the yield value for a finished good (it's already calculated in the table above for understanding purpose)

The logic for calculating it is - FG Qty / (RM Qty with maximum RM Cost)

Eg: For FG1, raw material with max cost is RM3. So its yield value will be 1000/300.

I'm facing difficulty to write a DAX measure for yield.

Please provide suggestions if any.

[v-jiascu-msft]

Hi @bidevsugmen,

Can you mark the proper answer as a solution please?

Best Regards,

Dale

[Greg_Deckler · Accepted solution]

Perhaps something like:

Yield = 
VAR maxRMCost = MAX(Yields[RM Cost])
VAR tmpTable = FILTER(Yields,[RM Cost]=maxRMCost)
RETURN SUMX(tmpTable,[FG Qty]) / SUMX(tmpTable,[RM Qty])

[Zubair_Muhammad]

@bidevsugmen

Another way.. to have a column like

Yield =
VAR maxCost =
    CALCULATE ( MAX ( Table1[RM Cost] ), ALLEXCEPT ( Table1, Table1[Batch] ) )
RETURN
    Table1[FG Qty]
        / CALCULATE (
            SUM ( [RM Qty] ),
            FILTER ( ALLEXCEPT ( Table1, Table1[Batch] ), Table1[RM Cost] = maxCost )
        )