Sumif in DAX

[Spartanos]

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])))

[Spartanos]

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]

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]

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                   

[AilleryO · Accepted solution]

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

[Spartanos]

Hi,

Thanks for your help, the first DAX formule works, the second gave syntax errors.

But the strange thing is that the formule didn't return the correct net amount.

tracking                 Net Amt Eur

16431178WRT     0

16431178WRT   12,73

16431178WRT   324,41

The DAX engine returned 304,53, which is incorrect. It should be 337,14. For some other tracking numbers, the sum is correct, while for others it isn't. Could this be caused due to that the tracking nr is a text format, and the DAX engine is not picking the correct  tracking nr?

[AilleryO]

Hi,

For the formula with summarize it would work if you create it as a new Table, not as a new column or Measure.

For the formula you made, there are no reason why it should be linked to text format, I've done it many times whith Ref Number, or Category in text and it worked like a charm.

In your example above you have 3 times the same tracking number is it normal ?

Could you give us some dummy data so we can have a deeper look ?

[Spartanos]

Hi,

I figured  out the issue. And how do I a add two filters, one for the currency (I only want to take euro's) and one for charge type 'fuel'? The formula below is not working.

Sum_ifs_tracking_fuel =

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

RETURN

CALCULATE(

FILTER(

'Append',

'Append'[Charge Type]="Fuel"),

&&'Append'[Currency]="Euro")

SUM('Append'[Net Amt Eur]),'Append'[Tracking Nr]=CurrentTrackingNum)))

)

)

[AilleryO]

You should not close the ) after ="Fuel".

Does it works ?

[Spartanos]

I have this code now, I would like to skip blank tracking numbers, and net charge >0 euro. Is this possible as well?

Sum_ifs_tracking_fuel =

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

RETURN

CALCULATE(

SUM('Append'[Net Amt Eur]),'Append'[Tracking Nr]=CurrentTrackingNum,

FILTER(

'Append',

'Append'[Charge Type]="Fuel Charges"

)

)

[AilleryO]

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