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.

Nonessential's avatar
Nonessential
Frequent Visitor
1 year ago

Hi, try this Laura1996  - Data is the table name so replace with your table name (KOMM) and I have called the Measure 'New Factor' instead of 'Final Factor';

 

New Factor =
VAR _Tbl =
ADDCOLUMNS(
SELECTCOLUMNS(Data,Data[Name],Data[Description],Data[Amount],Data[Factor (comes from another table called "Factors")],Data[Unit]),
"Final Factor", IF(
    Data[Unit] IN {"M", "GA", "L","KG"},
    CALCULATE(SUM(Data[Factor (comes from another table called "Factors")])),
    CALCULATE(SUMX(Data,Data[Amount]*Data[Factor (comes from another table called "Factors")]))
))

RETURN SUMX(_Tbl,[Final Factor])
Laura1996's avatar
Laura1996
Helper I
1 year ago

hey Nonessential ðŸ™‚

thank you for your try, unfortunately, The Factor is not in the same table, so the code is not working. I marked the problem in red, tried i with the relate function or to summarize, however don't know how to make it work:

New Factor =
VAR _Tbl =
ADDCOLUMNS(
SELECTCOLUMNS(Data,Data[Name],Data[Description],Data[Amount],Data[Factor (comes from another table called "Factors")],Data[Unit]),
"Final Factor", IF(
    Data[Unit] IN {"M", "GA", "L","KG"},
    CALCULATE(SUM(Data[Factor (comes from another table called "Factors")])),
    CALCULATE(SUMX(Data,Data[Amount]*Data[Factor (comes from another table called "Factors")]))
))

 

RETURN SUMX(_Tbl,[Final Factor])
  • Nonessential's avatar
    Nonessential
    Frequent Visitor
    1 year ago

    Hi Laura1996 , Could you bring the Factor from your Factors table into your KOMM table using a calculated column? Something like; 

    LOOKUPVALUE(
            'Factors'[Factor],
            'KOMM'[Description],
            'Factors'[Description])
    This assumes that there are 2 matching/correlating columns, one in KOMM and another in Factors - in the example above the matching columns are Description but you could swap these out if necessary.