topic Re: Sumif in DAX in DAX Commands and Tips

Sumif in DAX

Spartanos — Thu, 07 Apr 2022 12:14:12 GMT

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

Re: Sumif in DAX

AilleryO — Wed, 06 Apr 2022 14:04:07 GMT

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

Re: Sumif in DAX

Spartanos — Wed, 06 Apr 2022 14:22:15 GMT

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.

Re: Sumif in DAX

AilleryO — Wed, 06 Apr 2022 14:39:32 GMT

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

Re: Sumif in DAX

Spartanos — Thu, 07 Apr 2022 06:26:51 GMT

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

Re: Sumif in DAX

AilleryO — Thu, 07 Apr 2022 07:06:28 GMT

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

Re: Sumif in DAX

Spartanos — Thu, 07 Apr 2022 08:13:23 GMT

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?

Re: Sumif in DAX

AilleryO — Thu, 07 Apr 2022 08:39:26 GMT

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 ?

Re: Sumif in DAX

Spartanos — Thu, 07 Apr 2022 12:37:42 GMT

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

)

Re: Sumif in DAX

AilleryO — Thu, 07 Apr 2022 16:57:13 GMT

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

Does it works ?

Re: Sumif in DAX

Spartanos — Fri, 08 Apr 2022 10:49:29 GMT

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"

)