SumIf in Dax

[nmck86]

Hi All,

I am trying to translate a calculation I created in excel into Power Bi and am running into major issues. I was hoping that someone could take a look at the two attached files and give me some insight on what is going on. The excel file has the results that I am looking for.

https://docs.google.com/file/d/1-aXWaQbD_2Fh4ts6w51KhjQ53fWfbMFa/edit?usp=docslist_api&filetype=msex...

https://drive.google.com/open?id=1HN8Z1COYbUMyT2rptIkHfWd6RupbvHuN

[v-jiascu-msft]

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

[nmck86 · Accepted solution]

I was able to get the solution! Another user was able to help! I can share the formula if needed. @danextian was able to help out!