Forum Discussion

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

Sumifs is not working

I have got a simple sumifs, but for some reason I am doing something wrong.  I try to calculate the sum of a tracking number if the 'type'= 'residential-adjustment. The countif function is working. 

 

The goal is to get for tracking number IZ1234 a sumifs amount of 3+3+3+2=11.

 

Sum_ifs_tracking_fuel =
VAR CurrentTrackingNum=SELECTEDVALUE( 'Append'[Tracking number] )
RETURN

CALCULATE(
    SUM('Append'[charge]),'Append'[Tracking number]=CurrentTrackingNum,
FILTER(
    'Append',
    'Append'[type]="residential-adjustment"
)

)
 
Need Help
  • Shaurya's avatar
    Shaurya
    3 years ago

    Hi Spartanos,

     

    A much better way of getting the result that you want is to use SUMX along with EARLIER. This will add the charge column's numbers only when the tracking number is same and you can incorporate your condition for type column value to be "residential-adjustment" as well.

     

    Here's the formula:

     

    Sum_ifs_tracking_fuel = SUMX('Append',IF('Append'[Tracking number]=EARLIER('Append'[Tracking number]) && 'Append'[type]="residential-adjustment",'Append'[charge],0))

     

    I've tried it in your PBIX and it works.

     

     

    Mark this post as a solution if that works for you!

     

     

  • AilleryO's avatar
    AilleryO
    3 years ago

    Hi,

     

    Two things to do for it to work.

    The good news is your formula is good, but it should be a measure and not a calculated column.

    So just keep your formula as it is but paste it in a Measure.

    Then create a visual with the tracking number (you use allselected tracking numbers, so it must be in your visual) and your measure and you'll get what you expect.

     

     

    Let us know 🙂

3 Replies

  • Shaurya's avatar
    Shaurya
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi Spartanos,

     

    A much better way of getting the result that you want is to use SUMX along with EARLIER. This will add the charge column's numbers only when the tracking number is same and you can incorporate your condition for type column value to be "residential-adjustment" as well.

     

    Here's the formula:

     

    Sum_ifs_tracking_fuel = SUMX('Append',IF('Append'[Tracking number]=EARLIER('Append'[Tracking number]) && 'Append'[type]="residential-adjustment",'Append'[charge],0))

     

    I've tried it in your PBIX and it works.

     

     

    Mark this post as a solution if that works for you!

     

     

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

    Hi,

     

    Two things to do for it to work.

    The good news is your formula is good, but it should be a measure and not a calculated column.

    So just keep your formula as it is but paste it in a Measure.

    Then create a visual with the tracking number (you use allselected tracking numbers, so it must be in your visual) and your measure and you'll get what you expect.

     

     

    Let us know 🙂

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

    Hi,

    So you have 2 solutions 🙂

    The one from Shaurya needs a row context so is perfect as a column, whereas the one with a variable is more Measure oriented.

    Even though the EARLIER could work as well as a measure since the SUMX function is an iterative function, which means it creates the unexisting row context.

    Hope it helps and make things more clear.

    Let us know what is the best for you and do not forget to mark your post as solved.