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 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
1 year 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 😄

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Laura1996 ,

    Is your date data column inside the komm table? How are you filtering the dates? slicer or Filters pane.

    Try using the following DAX formula.

     

    Faktor final = 
SUMX(
    Komm,
    SWITCH(
        TRUE(),
        Komm[Unit] IN {"M", "GA", "L", "KG"}, Komm[Factor] * 1,
        Komm[Amount] * RELATED('Factor'[Factor])
    )
)

     

     

    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

    hey Anonymous ,

    in the end, your first code was the solution 🙂 the problem of the wrong calculation was another column, which I didn't use in the report. It was like an invoice number. 

    So let's say there are invoices from July, August and September.
    In July John bought 2 Apples, in August 3 and in September 1.
    What PowerBI did was: show 1 Apple for September but count 1 times 6, took this as the "amount" and multiplied it by the factor. Why 6? Well it took me some time to figure that out. Because the amount of all the Apples over all receipts is 6.

    anyway I fixed this and now your solution works like a charm 🙂 just wanted to post the solution here, because someone might stumble over the same problem. Watch your columns guys !! also if you're not using them in a specific report 🙂

    Thank you so much for the help