Variance Calculation Error (Summarize)

[Anonymous]

Hi All,

This sould be an easy fix but I am having an issue with the total variance calculation I am trying to do for price. Essentially the user has two tables to select the current period vs the previous period and then I am running a Price Variation calculation off of it:

(Average Current Price - Average Previous Price) * Current Volume

I want this action done at the item level however the total is off. Below is my equation and a picture of the results. Any idea what I am doing wrong in the calc? It looks like its working at the row level but then is off when I do the total.

Measure =

VAR AvgCurPrice = SUMX(

SUMMARIZE('Current Variance','Current Variance'[item],

"Cur Avg Price",[Current Avg Price]),[Cur Avg Price])

VAR AvgPrevPrice = SUMX(

SUMMARIZE('Previous Variance','Previous Variance'[item],

"Prev Avg Price",[PAvg Price]),[Prev Avg Price])

VAR CurVol = SUMX(

SUMMARIZE('Current Variance','Current Variance'[item],

"Cur Avg Vol",[Current Vol]),[Cur Avg Vol])

RETURN SUMX(

SUMMARIZE('Current Variance','Current Variance'[item],

"Variance",(AvgCurPrice-AvgPrevPrice)*CurVol),[Variance])

[v-xiaotang]

Hi @Anonymous 

Based on your explanation, I create a sample file and try to reproduce your problem. Unfortunately, it goes wrong. 

So can you share more details, or PBI sample file after removing sensitive information better?

Besides, what’s the result you want? Can you mark the result you want on the picture? Because I have little understanding of your words  that “I want this action done at the item level”.

Best Regards,

Community Support Team _ Tang

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

[daxer-almighty]

You have to describe what you want the total to be first. If you want the calculation to be done item by item and then the results for all visible items somehow aggregated, then you have to do just that. Your current formula does not do it. On top of that, variables once calculated do not change their values and they are immune to context. Variables do not behave like measures.