Forum Discussion

ssharm43's avatar
ssharm43
Icon for Helper I rankHelper I
6 years ago
Solved

Need Help in calculating sum with condition

Hello guya, Hope you all are safe, I have a requirment where I need to calculate sum of quantity based on certain filter and condition. conditions are: 1. if Type = "inf" then only calculate sum ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    You can just add that into the Calculate()

     

    SumIF inf %A% =
CALCULATE (
    SUM ( 'Type'[QUANTITY] ),
    'Type'[QUANTITY] > 0,
    FILTER (
        'Type',
        OR (
            'Type'[Type] <> "inf",
            AND ( 'Type'[Type] = "inf", SEARCH ( "{A}", 'Type'[Con_type],, 0 ) > 0 )
        )
    )
)

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

I am not sure what your second condition means, but here is an expression that I believe does #1 (if not also #2).

 

SumIF inf %A% =
CALCULATE (
    SUM ( 'Type'[QUANTITY] ),
    FILTER (
        'Type',
        OR (
            'Type'[Type] <> "inf",
            AND ( 'Type'[Type] = "inf", SEARCH ( "{A}", 'Type'[Con_type],, 0 ) > 0 )
        )
    )
)

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

  • ssharm43's avatar
    ssharm43
    Icon for Helper I rankHelper I
    6 years ago

    Thank you for this one, 

    It worked for 1st condition but not for second one.

    Let me elaborate a little,

    if you see the table, it has 0 and negative values in quantity. When a user export this underlying dataset they see 0 and negative values, which I don't want them to show. I want that, if user export they only see values greater than 0. The meausre I have uploaded was giving me that result as I was hiding the original field and giving them this measure to export.

     

    see below picture, I want to avoid those zeros values in drill down and export, therefore taking sum of booked quantity greater than zero.

    • mahoneypat's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      You can just add that into the Calculate()

       

      SumIF inf %A% =
CALCULATE (
    SUM ( 'Type'[QUANTITY] ),
    'Type'[QUANTITY] > 0,
    FILTER (
        'Type',
        OR (
            'Type'[Type] <> "inf",
            AND ( 'Type'[Type] = "inf", SEARCH ( "{A}", 'Type'[Con_type],, 0 ) > 0 )
        )
    )
)

       

      If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

      Regards,

      Pat

      • ssharm43's avatar
        ssharm43
        Icon for Helper I rankHelper I
        6 years ago

        It didn't perfectly, but it gave me a Idea which worked for me.

        Thank you very much for your help