Forum Discussion

DonPepe's avatar
DonPepe
Helper II
4 years ago
Solved

Dax Addition incorrect

Hi,

 

Do you know what is the cause of my false addition ? 35,00+3,00 is 38,00 not 38,75 or 38,21 or...

See below : in HperOSP, I add OSPDur to Boni If I have a Y in the table. here the measure :

 

HperOSP = 
var _SumBoni =
    CALCULATE (
        ( [OSPDur]+([Boni]) ),
        CurrentWeek[RustBoni] = "Y"
    )
var _SumNoBoni =
    CALCULATE (
        [OSPDur],
        CurrentWeek[RustBoni] = "N"
    )
return
    _SumBoni + _SumNoBoni

 

the result : (when "OSPDur = 35,00"  CurrentWeek[RustBoni] = "Y")

Thanks a lot for your help,

 

Don 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi DonPepe ,

     

    Firstly, create a flag measure to filter the table:

    Flag = IF(MAX('CurrentWeek'[Activity Name])="Rust" && MAX('CurrentWeek'[RustBoni]) = "Y" ,1,0)
    OSPDur = CALCULATE(SUM(CurrentWeek[PLA Duration (seconds)]) /3600,FILTER(ALL(CurrentWeek), [Activity Name]<>"Rust"))
    Boni = [OSPDur]*0.6/7

    Output is the same as your Test table returns:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi DonPepe ,

     

    Firstly, create a flag measure to filter the table:

    Flag = IF(MAX('CurrentWeek'[Activity Name])="Rust" && MAX('CurrentWeek'[RustBoni]) = "Y" ,1,0)
    OSPDur = CALCULATE(SUM(CurrentWeek[PLA Duration (seconds)]) /3600,FILTER(ALL(CurrentWeek), [Activity Name]<>"Rust"))
    Boni = [OSPDur]*0.6/7

    Output is the same as your Test table returns:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Tahreem24's avatar
    Tahreem24
    Super User
    4 years ago

    DonPepe Can you share the sample data (by copy pasting or excel) here for better solving at my end?

     

  • DonPepe's avatar
    DonPepe
    Helper II
    4 years ago

    Hi ,

     

    Thanks for your time. 

     

    I found the error and I have a granularity issue. 

     

    So I have one ID who make several task with a certain durantion during his day and my measure should represent :

    If when 'CurrentWeek'[Activity Name]="Rust", 'CurrentWeek'[Boni] = "Y" then [OSPDur]+[Boni] --the measure else [OSPDur] -- the measure

    But I dont know how to translate it in DAX. 

     

    here a sample of my data : 

    ID Activity NamePLA Duration (seconds)RustBoni
    CLD334Te voet ingang >< bureau12N
    CLD334Laden6N
    CLD334Laden1119Y
    CLD334Traject Parking >< Kade - camion fix93N
    CLD334Afleveren voertuig310N
    CLD334Te voet Kantoor >< Voertuig185N
    CLD334Sleutels, documenten en scanner teruggeven310N
    CLD334Rust2700Y
    CLD334Lossen1269N
    CLD334Te voet Kantoor >< Voertuig180N
    CLD334Sleutels, documenten en scanner teruggeven300N
    CLD334Te voet ingang >< bureau0N

     

    Thanks a lot,

     

    Don

    • Tahreem24's avatar
      Tahreem24
      Super User
      4 years ago

      DonPepe  Try this DAX:

      = IF(CurrentWeek'[Activity Name]="Rust" && 'CurrentWeek'[Boni] = "Y" ,[OSPDur]+[Boni] ,[OSPDur])

       

      • DonPepe's avatar
        DonPepe
        Helper II
        4 years ago

        Tahreem24,

         

        It worked in a calculated column but not in a measure. 

         

        How can I put the condition on the line "Rust" and sum on all the ID ? 

         

        Thanks,

         

        Don

    • DonPepe's avatar
      DonPepe
      Helper II
      4 years ago

      Ok I found a way with a new calculated table. I would have prefered with a measure but I dont master DAX enough. 
      Here the code of the new table :

      Test = 
SUMMARIZECOLUMNS(
    CurrentWeek[ID],
    "BoniYN",
    IF(
        ISBLANK(
            CALCULATE(
                MAX(CurrentWeek[RustBoni]),
            CurrentWeek[Activity Name]="Rijden"
            )
        ),
        MAX(CurrentWeek[RustBoni]),
        CALCULATE(
            MAX(CurrentWeek[RustBoni]),
        CurrentWeek[Activity Name]="Rijden"
        )        
    ),
    "OSPDur",
    CALCULATE(
        SUM(CurrentWeek[PLA Duration (seconds)])/3600,
        CurrentWeek[Activity Name]<>"Rust"
    ),
    "Boni",
    ((
    CALCULATE(
        SUM(CurrentWeek[PLA Duration (seconds)])/3600,
        CurrentWeek[Activity Name]<>"Rust"
    )
    )*0.6)/(7)
)



      Don