Forum Discussion

TrevLc's avatar
TrevLc
Helper 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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,TrevLc,I am glad to help you.
Hello, amitchandak .Your answer is excellent!
And I would like to share some additional solutions below

 

Based on your description, you want to calculate the difference between each row and the previous row, and aggregate those differences at the position of total to calculate the sum of the differences, not the sum of all quantities

The problem with your totals displaying incorrectly stems from the way totals are calculated in Power BI, which often doesn't match the expected result when using row-level logic for each item in the visual object

Here is my test result.

I created the calculate column to accomplish your needs:
here is the DAX code:

 

C_Difference_ = 
VAR CurrentValue = [Actual]
VAR PreviousValue = CALCULATE(
    MAX([Actual]),
    FILTER(
        ALL('Table'),
        'Table'[Date] < EARLIER('Table'[Date])
    )
)
RETURN
CurrentValue- PreviousValue

 



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
Helper III
2 years ago

Thank you... this will work. I was just hoping I could do it using visual level calculations

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,TrevLc ,thank you for your reply.
    You can try the following test

     

     

    Versus previous = 
var prev =  [M_value] - PREVIOUS([M_value])
VAR differ=
SUMX(VALUES([Date]),
CALCULATE(prev)
)
return
IF(ISINSCOPE([Date]),
differ,
[Sum of C_Difference_]
)

     

     

    I forced the value of Total to be changed via ISINCOPE, but it still used the value of the previously created calculated column itself, I tried to use [M_value] but the result was wrong, in the end I chose to use [Sum of C_Difference]

     

     

    C_Difference_ = 
VAR CurrentValue = [Actual]
VAR PreviousValue = CALCULATE(
    MAX([Actual]),
    FILTER(
        ALL('Table'),
        'Table'[Date] < EARLIER('Table'[Date])
    )
)
RETURN
CurrentValue- PreviousValue
// IF(ISBLANK(PreviousValue),0, CurrentValue - PreviousValue)
// IF(ISBLANK(PreviousValue),'Table'[Actual], CurrentValue - PreviousValue)

    M_value = SUM('Table'[Actual])

     

     

    I noticed that your [Actual] has to be measure instead of columns

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