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.

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

 

 


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.

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

    Saxon10 ROUND is a good modification but rounding to zero decimal places might be a bit too round. If you run into trouble, try ROUND ( SumIfs, 1 ) instead.

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

      AlexisOlson ,

       

      Thanks for your tips and advise. I will test both scenario at my end.