Totals incorrect for visual level calculation

[TrevLc]

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

[Anonymous]

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]

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

[Anonymous]

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]

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 · Accepted solution]

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.

[amitchandak]

@TrevLc , I doubt as of now you can use previous values in GT.

You can log an Idea of Issue

https://community.powerbi.com/t5/Issues/idb-p/Issues

https://ideas.powerbi.com/ideas/

[TrevLc]

Thanks for your response.

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