SumIf in Dax

Hi nmck86,

Please check out the demo here.

I would suggest you use the original data instead of the unpivot data. For example below, filtering out the row in the red rectangle from the unpivot table would be very hard.

Water Quality =
DIVIDE (
    CALCULATE (
        SUM ( 'Result'[Weights] ),
        FILTER (
            'Result',
            'Result'[likeliness to recommend] >= 9
                && 'Result'[water quality] = EARLIER ( Rankings[Value] )
        )
    )
        - CALCULATE (
            SUM ( 'Result'[Weights] ),
            FILTER (
                'Result',
                'Result'[likeliness to recommend] <= 6
                    && 'Result'[water quality] = EARLIER ( Rankings[Value] )
            )
        ),
    CALCULATE (
        SUM ( 'Result'[Weights] ),
        'Result'[water quality] = EARLIER ( Rankings[Value] )
    )
)

Water Prices =
DIVIDE (
    CALCULATE (
        SUM ( 'Result'[Weights] ),
        FILTER (
            'Result',
            'Result'[likeliness to recommend] >= 9
                && 'Result'[water price] = EARLIER ( Rankings[Value] )
        )
    )
        - CALCULATE (
            SUM ( 'Result'[Weights] ),
            FILTER (
                'Result',
                'Result'[likeliness to recommend] <= 6
                    && 'Result'[water price] = EARLIER ( Rankings[Value] )
            )
        ),
    CALCULATE (
        SUM ( 'Result'[Weights] ),
        'Result'[water price] = EARLIER ( Rankings[Value] )
    )
)

Best Regards,

Dale