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 that still did not work.

Here is my formula

Versus previous =
var prev =  [Actual] - PREVIOUS([Actual])
return
SUMX(VALUES([Date]),
CALCULATE(prev)
)
Here is my data, and as you can see, the total for "Versus previous" is the same as the total for "Actual"
 
Please help, I want the total to be the sum of the line items.
Note, this is specific to the visual level calculation. "Versus previous" is not a measure

 

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.

7 Replies

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

      Thanks for your response.

      Do you know a way I can sum the line items for the visual level calculation?

  • 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
      Icon for Helper III rankHelper 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.