Forum Discussion

TrevLc's avatar
TrevLc
Icon for Helper III rankHelper III
2 years ago
Solved

Totals incorrect for visual level calculation

I created a visual level calculation, which is quite simple -> versus previous, which works on a line by line level, but the total was incorrect. So I changed the DAX to use SUMX and Values, but tha...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,TrevLc 
    Thank you for your replay
    If you have more than one product and are calculating the difference between the current day's and previous day's quantities for each product on a row-by-row basis, and ultimately returning the correct sum of the differences, you can refer to the test I've done below
    Here is my test result: 

    Versus previous = 
VAR prev=IF([Product]=PREVIOUS([Product]),
[M_Amount_value] - PREVIOUS([M_Amount_value]),
[M_Amount_value])
VAR differ=
SUMX(VALUES([Date]),
CALCULATE(prev)
)
return
IF(ISINSCOPE([Date]),
differ,
[Sum of C_Difference_3]
)

    C_Difference_3 = 
VAR CurrentValue=[Amount]
VAR PreviousValue = CALCULATE(
    MAX('Table2'[Amount]),
    FILTER(
        ALL('Table2'),
        'Table2'[Product] = EARLIER('Table2'[Product]) &&
        'Table2'[Date] < EARLIER('Table2'[Date])
    )
)
RETURN

CurrentValue -PreviousValue

    Here is  my test data:

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

    Best Regards,

    Carson Jian,

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

TrevLc's avatar
TrevLc
Icon for Helper III rankHelper III
2 years ago

Thanks so much.

How would I adjust the formula if I had multiple products? So each product has an amount per date... for example, here could be some data

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,TrevLc 
Thank you for your replay
If you have more than one product and are calculating the difference between the current day's and previous day's quantities for each product on a row-by-row basis, and ultimately returning the correct sum of the differences, you can refer to the test I've done below
Here is my test result: 

Versus previous = 
VAR prev=IF([Product]=PREVIOUS([Product]),
[M_Amount_value] - PREVIOUS([M_Amount_value]),
[M_Amount_value])
VAR differ=
SUMX(VALUES([Date]),
CALCULATE(prev)
)
return
IF(ISINSCOPE([Date]),
differ,
[Sum of C_Difference_3]
)

C_Difference_3 = 
VAR CurrentValue=[Amount]
VAR PreviousValue = CALCULATE(
    MAX('Table2'[Amount]),
    FILTER(
        ALL('Table2'),
        'Table2'[Product] = EARLIER('Table2'[Product]) &&
        'Table2'[Date] < EARLIER('Table2'[Date])
    )
)
RETURN

CurrentValue -PreviousValue

Here is  my test data:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.