Forum Discussion

datajunkie_29's avatar
datajunkie_29
Helper I
3 years ago
Solved

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:

BranchIdCustIdFinalMeasure
701 $ 234.00
652 $ 156.00
653 $ 786.00
541 $ 156.00



Any help is appreciated! Thank you

Aggregation feature
DAX help needed
  • johnt75's avatar
    johnt75
    3 years ago

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

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

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