Forum Discussion

Icon for Helper II rank

Helper 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_...

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

Icon for Helper II rank

Helper 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
Memorable 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
  Helper 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
  - AilleryO
    Memorable Member
    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

Related content