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.

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

5 M * 10 should equal 10, because thats what the formula says. But it equals 20. The same thing with 6 M. 

Because I have to be able to filter also the month / week / day of a persons collected products this triggers the mistake - at least this is what I think. Because when I remove the filter its correct. I never had this problem in any other reports. Is there any way to also include month / week / day into the calculation?

here a screenshot of the date:

the week number I extracted by myself, so I can use it in a calculation. Do I have to do the same stuff for day/month, and how would the code look like, because HASONEVALUE() can work just with one value as far as Im concerned.

Thank you very much





Anonymous's avatar
Anonymous
Not applicable
2 years 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
    2 years 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
      2 years 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
        2 years 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 😄