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.

Irwan's avatar
Irwan
Super User
1 year ago

hello Laura1996 

 

please check if this accomodate your need.

 

create a new measure with following DAX.

Measure = 
var _Name = SELECTEDVALUE('Table'[Name])
Return
IF(
    HASONEVALUE('Table'[Product]),
    SUMX(
        ALLSELECTED('Table'),
        IF(
            'Table'[Unit]="ST",
            'Table'[Amount]*'Table'[Factor (comes from another table called "Factors")],
            'Table'[Factor (comes from another table called "Factors")]
        )
    ),
    SUMX(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Name]=_Name
        ),
        IF(
            'Table'[Unit]="ST",
            'Table'[Amount]*'Table'[Factor (comes from another table called "Factors")],
            'Table'[Factor (comes from another table called "Factors")]
        )
    )
)
 
Hope this will help.
Thank you.
Laura1996's avatar
Laura1996
Helper I
1 year ago

hello Irwan 🙂

Unfortunately it doesn't do what it should...as the field "Factor" comes from another table and this table is not mentioned in "ALLSELECTED" he cant find the field. Also ALLSELECTED allows just one argument.

I tried to solve it over related, but its making strange calculations. With my measure they were right and just the total was wrong for the rows selected, now he calculates 6* 0,88 = 3.709,60 which makes no sense. In fact the result is 3709,60 for all the rows.

Also in your Screenshot he calculates 6+3 as 21, when it should be 9. And when you dont select anything he calculates it as 31, which is also not correct...
it actually should be a super simple measure but it became lemon difficult 😄 thank you anyway for trying, hope someone can provide me a working solution

  • Laura1996's avatar
    Laura1996
    Helper I
    1 year ago

    Also I need to focus on this "collecting= "M" || collecting="GA" || collecting="L" || collecting="KG"

    the measure shouldn't work with "ST", because there are many more units than just those mentioned up. So when I make an if statement, I have to define which to "exclude" rather than which to consider.

    • Irwan's avatar
      Irwan
      Super User
      1 year ago

      Wait, you said in your first post, Jack has value of 21 which is 6+15. 

      I thought 6 (2*3 because ST) and 15 (5*3 because ST), no?

       

      What value for Jack, 9 or 21?

       

      And when not selected, 6 (2*3 ST) + 15 (5*3 ST) + 6 (6*1 M) + 4 (4*1 KG) = 31

       

      Also those M, GA, L, KG in if statement, as far as i can tell, this only for differentiate which will be multiplied and not. I only put ST for multiplying and you can add the rest.

      • Laura1996's avatar
        Laura1996
        Helper I
        1 year ago

        Yes sorry, that was my mistake now because I didn't seen the number 15 in your table. 21 should be correct. Also 31 is correct, however its not working for my data, because my result is always 3709,60. And also in your code I can't add the other table for the Factor (Table "Factor").

        Actually trying to add the rest is not working or at least I didn't still figure out how to make it work...