Forum Discussion

nmck86's avatar
nmck86
Icon for Post Patron rankPost Patron
8 years ago
Solved

SumIf in Dax

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=msexcel

 

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

 

 

Need Help
  • nmck86's avatar
    nmck86
    8 years ago
    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!

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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.

    Sum_If_in_Dax

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

    Sum_If_in_Dax2

     

    Best Regards,

    Dale

    • nmck86's avatar
      nmck86
      Icon for Post Patron rankPost Patron
      8 years ago
      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!