Forum Discussion

Laura1996's avatar
Laura1996
Icon for Helper I rankHelper I
2 years ago
Solved

wrong measure total including if statement

I have a table "Komm", which looks like following. Right now my Total of "Factor final" is wrong and I cant wrap my head arount how to do it correctly:   Name Product Description Amount Unit...
DAX
if statement
measure
Totals
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Laura1996 ,

    Based on the description, try to use the following DAX formula.

    Faktor final = 
VAR IsSpecificUnit = 
    SUMX(
        Komm, 
        IF(
            Komm[Unit] IN {"M", "GA", "L", "KG"}, 
            RELATED('Factor'[Factor]), 
            Komm[Amount] * RELATED('Factor'[Factor])
        )
    )
RETURN 
    IF (
        HASONEVALUE(Komm[Name]),
        IsSpecificUnit,
        SUMX(
            Komm,
            IF (
                Komm[Unit] IN {"M", "GA", "L", "KG"},
                RELATED('Factor'[Factor]),
                Komm[Amount] * RELATED('Factor'[Factor])
            )
        )
    )

    Then, select the Jack name.

     

    Best Regards,

    Wisdom Wu

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Laura1996 ,

Based on the description, try using the following DAX formula.

Faktor final = 
VAR IsSpecificUnit = 
    SUMX(
        Komm, 
        IF(
            Komm[Unit] IN {"M", "GA", "L", "KG"}, 
            RELATED('Factor'[Factor]), 
            Komm[Amount] * RELATED('Factor'[Factor])
        )
    )
RETURN 
    IF (
        HASONEVALUE(Komm[Name]),
        CALCULATE(
        SUMX(Komm, Komm[Amount] * RELATED('Factors'[Factor])),
        FILTER(
            Komm,
            Komm[Name] = SELECTEDVALUE(Komm[Name]) &&
            Komm[Month] = SELECTEDVALUE(Komm[Month]) &&
            Komm[Week] = SELECTEDVALUE(Komm[Week]) &&
            Komm[Day] = SELECTEDVALUE(Komm[Day])
        )
    ), IsSpecificUnit

    )

 

Best Regards,

Wisdom Wu

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

Laura1996's avatar
Laura1996
Icon for Helper I rankHelper I
1 year ago

hi Anonymous ğŸ™‚

well, now its even worse 😄 this is the result:

so now its 2.818,00 instead of 53,50 and it kind of ignores the rule for "M, M2, KG,.."

this report makes me crazy 



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Laura1996 ,

    I forget the rule for M, M2. Please try using the following DAX formula.

    Faktor final = 
VAR IsSpecificUnit = 
    SUMX(
        Komm, 
        IF(
            Komm[Unit] IN {"M", "GA", "L", "KG"}, 
            RELATED('Factor'[Factor]), 
            Komm[Amount] * RELATED('Factor'[Factor])
        )
    )
RETURN 
    IF (
        HASONEVALUE(Komm[Name]),
        IF(
            Komm[Unit] IN {"M", "GA", "L", "KG"}, 
            RELATED('Factor'[Factor]), 
            CALCULATE(
                SUMX(Komm, Komm[Amount] * RELATED('Factors'[Factor])),
                FILTER(
                Komm,
                Komm[Name] = SELECTEDVALUE(Komm[Name]) &&
                Komm[Month] = SELECTEDVALUE(Komm[Month]) &&
                Komm[Week] = SELECTEDVALUE(Komm[Week]) &&
                Komm[Day] = SELECTEDVALUE(Komm[Day])
            )
        ), 
        IsSpecificUnit
    )

    Best Regards,

    Wisdom Wu

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

    • Laura1996's avatar
      Laura1996
      Icon for Helper I rankHelper I
      1 year ago

      hi Anonymous ğŸ™‚

      unfortunately now dax is telling me that I have too many arguments and that there can be a max. of 3:

      it specifically underlines (in the paragraph after the RETURN):

      Komm[Unit] IN {"M", "GA", "L", "KG"}, 
            RELATED('Factor'[Factor]),

      &

       IsSpecificUnit

      also he's not finding me the variable "Factor" or "Unit" in that if statement
      wish I could finally mark this as resolved...but its giving me a headache 😄

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Laura1996 ,

        Is your date data column inside the komm table? How are you filtering the dates? slicer or Filters pane.

        Try using the following DAX formula.

         

        Faktor final = 
SUMX(
    Komm,
    SWITCH(
        TRUE(),
        Komm[Unit] IN {"M", "GA", "L", "KG"}, Komm[Factor] * 1,
        Komm[Amount] * RELATED('Factor'[Factor])
    )
)

         

         

        Best Regards,

        Wisdom Wu

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