Conditional Aggregate Measure Help

[Anonymous]

I'm trying to make a blended cost measure/column/table (not sure what's appropriate here).

The data currently looks like this.

Estimated_Actual        AP_AR       ShipmentID     Cost
Actual                          AP                    1                 5.00
Actual                          AR                    1                 4.00
Estimated                    AP                    1                  4.00
Estimated                    AR                    1                  3.00
Actual                         AP                     2                 -
Actual                         AR                    2                  - 
Estimated                   AP                     2                 7.00
Estimated                   AR                    2                  8.00



I'd like to have a blended cost measure that will display Actual costs if those values exist, otherwise return the Estimated cost. Also needing HASONEVALUE(AP_AR) but that's separate from the issue at hand.

Basically if Estimated_Actual ="Actual" AND has a cost value, use that cost value, otherwise return the estimated cost. Sorry if this is explained poorly, I'm little out of my depth on this request. I was trying to use VAR Summarize to group by estimated/actual, but I'm kind of hitting the wall.

Any help would be appreciated.

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Per your requirements, create measures

Measure =
IF (
    MAX ( Sheet6[Estimated_Actual] ) = "Actual"
        && MAX ( Sheet6[Cost] ) <> BLANK (),
    CALCULATE (
        SUM ( Sheet6[Cost] ),
        FILTER (
            ALLEXCEPT ( Sheet6, Sheet6[ShipmentID], Sheet6[AP_AR] ),
            Sheet6[Estimated_Actual] = "Actual"
        )
    ),
    CALCULATE (
        SUM ( Sheet6[Cost] ),
        FILTER (
            ALLEXCEPT ( Sheet6, Sheet6[ShipmentID], Sheet6[AP_AR] ),
            Sheet6[Estimated_Actual] = "Estimated"
        )
    )
)

Best Regards

Maggie

[Anonymous]

Thank you for your help,

I think I was unclear about what I was looking for in my original post. Here's an example of the expected output for the new measure. The goal is to be able to remove the Estimated_Actual filter/column and not have Cost aggregate values from that filter together.

[v-juanli-msft]

Hi @Anonymous

Try this measure:

Measure 4 =
VAR flag =
    IF (
        CALCULATE (
            SUM ( Sheet6[Cost] ),
            FILTER (
                ALLEXCEPT ( Sheet6, Sheet6[ShipmentID], Sheet6[AP_AR] ),
                [Estimated_Actual] = "Actual"
            )
        )
            <> BLANK (),
        1,
        0
    )
RETURN
    IF (
        MAX ( [Estimated_Actual] ) = "Actual"
            && flag = 1,
        MAX ( [Cost] ),
        IF ( MAX ( [Estimated_Actual] ) = "Estimated" && flag = 0, MAX ( [Cost] ), " " )
    )

Best Regards

Maggie