Forum Discussion

Laura1996's avatar
Laura1996
Helper I
1 year 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
    1 year 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 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
Helper I
1 year ago

Hey, thank you for the answer.

Unfortunately something with the calculation still doesn't go right:

Here some results:

 

The strange thing is that sometimes the calculation is correct and sometimes not. I have no other sums in my visual...so I don't know what it could be... The sum here should be 53,50 considering that I added also M2 into your code... any ideas what it could be?

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