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