Incorrect totals when using % calculations with quantity and filters

[tradeexllc]

Hello,

I am trying to calculate PY Sales variance and each time I bring in the calculation for PY the totals do not add up. Here are all my formulas in order:

QTY PY =

IF (

ISCROSSFILTERED( Sheet1[INVOICE DATE].[Year]),

CALCULATE( sum(Sheet1[QTY SOLD]),

DATEADD('Sheet1'[INVOICE DATE].[Date], -1, YEAR)),

BLANK())

PY Total List Price =

IF (

ISCROSSFILTERED( Sheet1[INVOICE DATE].[Year]),

CALCULATE( sum(Sheet1[TOTAL LIST PRICE]),

DATEADD('Sheet1'[INVOICE DATE].[Date], -1, YEAR)),

BLANK())

PY Discount $ = sum(Sheet1[NET SALES])-[PY Total List Price]

PY Avg List Price/Ut = [PY Total List Price]/sum(Sheet1[QTY SOLD])

PY Discount % = ([PY Discount $]/[PY Total List Price])

Sales Discount Var PY = (sum(Sheet1[QTY SOLD])*[PY Discount %]) - this gives me incorrect totals. If I change PY Total List Price and PY QTY and remove filter dateadd, the totals will be correct. 

How to change the calculatiosn to give me the right totals?

Thank you!

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[amitchandak]

Multiplication should happen at line level or some group level.

So we we do Sum(A*B) Not sum(a)* sum(b)

Do do this is to take a row context

sumx(summarize(table,table[Date],"_sum",sum(Sheet1[QTY SOLD]),"_dis",[PY Discount %]),[_sum]*[_dis])

table[date] is the row context where calculation need to done, it can be id or month as per need

[tradeexllc]

Hi,

I tried your advise and used the following calcualtions:

test = sumx(summarize(Sheet1,Sheet1[INVOICE DATE].[Date],"_sum",sum(Sheet1[QTY SOLD]),"_dis",[PY Discount %]),[_sum]*[_dis])

but the result was infinity

Am I doing something wrong with calculations?

Thank you