Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

SUMIFS with multiple columns with DAX

  Hi,   How can I do sumifs in Power BI based on the below mentioned data.   In Excel I am apply the following formula =IF(OR($C3="Not Pair",SUMIFS(D$3:D$73,A$3:A$73,A3,C$3:C$73,C3)=1),$B3,"Not ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You can't have two columns with the same name, so I change the first to "Colour" and second to "Code":

     

    Result =
VAR SumIfs =
    CALCULATE (
        SUM ( Table1[Unique Count] ),
        ALLEXCEPT ( Table1, Table1[Item], Table1[Code] )
    )
RETURN
    IF ( Table1[Code] = "Not Pair" || SumIfs = 1, Table1[Colour], "Not Okay" )

     

  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Saxon10 ，

     

    Please add the ROUND function to the formula of AlexisOlson.

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

You can't have two columns with the same name, so I change the first to "Colour" and second to "Code":

 

Result =
VAR SumIfs =
    CALCULATE (
        SUM ( Table1[Unique Count] ),
        ALLEXCEPT ( Table1, Table1[Item], Table1[Code] )
    )
RETURN
    IF ( Table1[Code] = "Not Pair" || SumIfs = 1, Table1[Colour], "Not Okay" )

 

  • Saxon10's avatar
    Saxon10
    Icon for Post Prodigy rankPost Prodigy
    4 years ago

    AlexisOlson ,

     

    Thanks for your respones and sorry for the late reply.
    Your solution almost working fine but I recevied incorrect result were the same itms has repeat multiple times

     

     


    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      I'm guessing you have some kind of rounding error so that Unique Count isn't adding up to exactly 1.

       

      I don't understand why you have duplicated rows, so it's hard to suggest a fix.

    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi Saxon10 ，

       

      Please add the ROUND function to the formula of AlexisOlson.

       

       

      If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

      Best Regards,
      Winniz

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Saxon10's avatar
        Saxon10
        Icon for Post Prodigy rankPost Prodigy
        4 years ago

        Thanks for your reply and your solution. 

         

        Your solution working well. Thank you.