Forum Discussion

Spartanos's avatar
Spartanos
Icon for Helper II rankHelper II
4 years ago
Solved

Sumif in DAX

I want to make an easy sumif calulation with summing the sum of tracking numbers (text format). I also want to add a filter  with only rows which contain the currency euro.

tracking             net_amount  currency

1Z169943110    2                   euro

1Z169943110    4                   gbp

1Z169943118    6                   euro

 

Measure = SUMX(FILTER(Append,Append[Tracking Nr]),Append[Net Amt Eur])
I receive the follwoing error: 
'Cannot convert value 1Z169943110 of type text to type true/false.
 

I also tried this formula:

Measure 2 = CALCULATE(SUM(Append[Net Amt Eur]), FILTER(Append, Append[Tracking Nr] = EARLIER (Append_data_2022_2, Append_data_2022_2[Tracking Nr])))
  • AilleryO's avatar
    AilleryO
    4 years ago

    Hi,

     

    So it should work with :

    Sum_if = 
VAR CurrentTrackingNum=SELECTEDVALUE( 'Append'[Tracking Nr] )
RETURN

CALCULATE( SUM('Append'[Charge Net]),'Append'[Tracking Nr]=CurrentTrackingNum)

     

    or you can use SUMMARIZE :

    Create measure

    Tot Net = SUM( [Charge Net] )

    then :

    Tot by Track Num =
SUMMARIZECOLUMNS( [Tracking Nr] , "Name of new column", [Tot Net] )

     

    Tell us what's best for you

10 Replies

  • AilleryO's avatar
    AilleryO
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi,

     

    I'm not sure about what you're looking for, but if you want to do a SUMIF in DAX it will be something like that :

    CALCULATE( SUM( [Net Amt Eur] ) , [Currency] = "euro" )

     

    Hope it helps

     

  • Spartanos's avatar
    Spartanos
    Icon for Helper II rankHelper II
    4 years ago
    I have tried this formula, but I receive the followoing error: 
    'Cannot convert value 1Z169943110 of type text to type true/false. I think the issue is that the tracking number is in text format. If I run the sumif formula on only the currency, it works fine.
     
    I have this formula: 
    Sum_if = CALCULATE( SUM('Append'[Charge Net]),'Append'[Tracking Nr]), I will add the currency filter later.
    • AilleryO's avatar
      AilleryO
      Icon for Memorable Member rankMemorable Member
      4 years ago

      I do not understand the last part of your formula, why do you have 'Append'[Tracking Nr] by the end ?

      It's the part throwing the error.

      If you're trying to do your Sum If on the "current" 'Append'[Tracking Nr], may be you should try :

      Sum_if = 
      VAR CurrentTrackingNum=SELECTEDVALUE( 'Append'[Tracking Nr] ) or MAX ( 'Append'[Tracking Nr] )
      RETURN

      CALCULATE( SUM('Append'[Charge Net]),'Append'[Tracking Nr]=CurrentTrackingNum)

       

      The variable keeps the value of the Tracknig number on the line you're calculating, and is used as a filter to consider only the lines with same Tracking Num.

       

      Hope it helps

      • Spartanos's avatar
        Spartanos
        Icon for Helper II rankHelper II
        4 years ago

        Hi,

         

        The idea is to calculate the sum of het net amount per tracking number.

         

        This is the current table

        (text format)

        tracking nr            net amt         

        1Z169943110        1

        1Z169943110        2                   

        1Z169943118       10                   

        1Z169943118         6                   

         

        The idea is to sum this up per tracking nr, like this:

        tracking nr             net amt         

        1Z169943110         3                                 

        1Z169943118         16