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

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

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community 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
      Community 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 )
        )

       

       

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Can you mark the proper answer as a solution please?

     

    Best Regards,

    Dale