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

[ganesanr1002]

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

[tamerj1]

Hi @ganesanr1002 

please try

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

[ganesanr1002]

Hi @tamerj1  , 

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

[tamerj1 · Accepted solution]

@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]
)

[ganesanr1002]

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