DAX Aggregate by multiple fields

[datajunkie_29]

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

[datajunkie_29]

Hi @johnt75 , thank you for your response, the solution worked 🙂

[johnt75 · Accepted solution]

You can use something like

My Measure =
SUMX (
    SUMMARIZE ( Sales, Sales[BranchId], Sales[custid], Sales[itemid] ),
    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
        )
    )
)