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 ,
I would like to know how 53.5 is calculated? Which row values are added together to get the result?

What's more, try to check if any other filters are being applied.

 

Best Regards,

Wisdom Wu

Laura1996's avatar
Laura1996
Icon for Helper I rankHelper I
1 year 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
    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.