Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

GROUP BY logic

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 und...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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])

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

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's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Anonymous

 

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 )
        )