topic Re: sum of Variance Getting wrong ,anyone help to sort it out in DAX Commands and Tips

sum of Variance Getting wrong ,anyone help to sort it out

ganesanr1002 — Sat, 08 Jul 2023 17:49:11 GMT

Hi Guys ,

Here i trying to find the variance each of the line items it is matching exactly but the total of that it is not matching at all ,Can anybody help me to sort it out .

I have AOP, City, Item, COGS , Dim Date Tables ,

Here I'm Trying to get the variance for the COGS between Actual and AOP based on the City ,Item and Month and Year.

Table Structure

AOP

City

COGS

Dim Date

Item

Relationship

Measure Used for

Avg AOP Price = AVERAGE(AOP[Value])

Actual Price = SUM(COGS[Total])/SUM(COGS[Quantity])

Variance = [Avg AOP Price]-[Actual Price]

Can anybody Help me sort it out , Thanks in Advance

Re: sum of Variance Getting wrong ,anyone help to sort it out

tamerj1 — Sat, 08 Jul 2023 19:08:49 GMT

Hi ganesanr1002

please try

Variance =
SUMX (
CROSSJOIN (
CROSSJOIN ( VALUES ( Item[Item Nsme] ), VALUES ( City[City] ) ),
VALUES ( 'Dim Date'[Date] )
),
[Avg AOP Price] - [Actual Price]
)

Re: sum of Variance Getting wrong ,anyone help to sort it out

ganesanr1002 — Sat, 08 Jul 2023 20:45:41 GMT

Hi tamerj1 ,

Now total it is matching but as per the row not matching

Re: sum of Variance Getting wrong ,anyone help to sort it out

tamerj1 — Sun, 09 Jul 2023 00:02:43 GMT

ganesanr1002
Seems that in the table visual, you have placed the date column from the AOP table this is why you have wrong values for the Actual Price (which apparently you did not notice). You need to use the date column only from the date table as it filters both fact tables.

Variance = SUMX ( CROSSJOIN ( CROSSJOIN ( VALUES ( 'Item'[Item Name] ), VALUES ( City[City] ) ), VALUES ( 'Dim Date'[Date] ) ), [Avg AOP Price] - [Actual Price] )

Re: sum of Variance Getting wrong ,anyone help to sort it out

ganesanr1002 — Sun, 09 Jul 2023 04:04:46 GMT

tamerj1 thanks i forgot to check date column , It works , Thanks for the solution .