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

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

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's avatar
Spartanos
Icon for Helper II rankHelper II
4 years ago

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's avatar
    AilleryO
    Icon for Memorable Member rankMemorable Member
    4 years ago

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

    Does it works ?

  • Spartanos's avatar
    Spartanos
    Icon for Helper II rankHelper II
    4 years ago
    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"
    )
    )