DAX Aggregate by multiple fields
Hello all, I am fairly new to power bi and have come across a road blocker while creating a measure. I'm trying to aggregate the measure based on several fields from different but related tables. Dummy Data Model: Measures Calculated : Step1------------- MarginAmt_Y = CALCULATE(SUM(MarginAmt), FILTER(QL_Flag = "Y") MarginAmt_N = CALCULATE(SUM(MarginAmt), FILTER(QL_Flag = "N") NetAmt_Y = CALCULATE(SUM(NetAmt), FILTER(QL_Flag = "Y") NetAmt_Y = CALCULATE(SUM(NetAmt), FILTER(QL_Flag = "N") QtyAmt_Y = CALCULATE(SUM(Qty), FILTER(QL_Flag = "Y") Qty_Y = CALCULATE(SUM(Qty), FILTER(QL_Flag = "N") Step2 --------------------- Y_Margin/QTY = DIVIDE([NetAmt_Y] - [MarginAmt_Y], [Qty_Y]) N_Margin/QTY = DIVIDE([NetAmt_N] - [MarginAmt_N], [Qty_N]) Step3 ----------------------- FinalMeasure = IF ( OR([Y_Margin/QTY] = BLANK (), [N_Margin/QTY] = BLANK ()), 0, IF ( [Y_Margin/QTY] < [N_Margin/QTY], ( ([N_Margin/QTY] - [Y_Margin/QTY]) * [Qty_N]), 0 ) ) However, the final measure is returning a zero, as it is calculating at overall level. How do I agrregate it by CustId, BranchId, ItemId something like: Measure = SUM(Aggr( IF ( OR([Y_Margin/QTY] = BLANK (), [N_Margin/QTY] = BLANK ()), 0, IF ( [Y_Margin/QTY] < [N_Margin/QTY], ( ([N_Margin/QTY] - [Y_Margin/QTY]) * [Qty_N]), 0 )),BranchId, custid, itemid)) Desired Output: BranchId CustId FinalMeasure 70 1 $ 234.00 65 2 $ 156.00 65 3 $ 786.00 54 1 $ 156.00 Any help is appreciated! Thank you
