% Difference between Last Year WTD and This Year WTD for Online and Store Sales

[slavisha84]

Hi, 

I am trying to resolve the issue with averages.
I have a dataset like this one below:

So I have a fiscal week from 1 to 52. Within each week I Have Online and Store sales. I calculated variances by subtracting 2020 from 2021 Revenue which is fine. However, when I try to do the % difference per Fiscal week I am not getting the correct results overall. 
I do get the correct result per channel but if I select all I am I am getting wrong results. 
The reason is that Power BI calculates WTDYOY% for each line separately. So since I have FiscalWeek for Online and Fiscal Week For Store, the WTDYOY% will be calculated separately for each of these. And I get the correct results. But when I select all PowerBI try to add or average results for Online WTDYOY% and Store WTDYOY% which is not correct. 
I need to be able to present Fiscal week overall, Online, and CSR separately and correct. 

How do I fix this?

Here is the folder where i have sample of data:
https://1drv.ms/u/s!AhhZq1add5YwjYIvuASi76lCL3R1eA?e=sZQshq

. 

[jdbuchanan71 · Accepted solution]

@slavisha84 

Rather then putting your results in a new table you should calculate them with measures.

Revenue Amount = SUM ( RevenueByDate[Revenue] )

2020 Revenue = CALCULATE ( [Revenue Amount], FiscalCalendar[FiscalYear] = 2020 )

2021 Revenue = CALCULATE ( [Revenue Amount], FiscalCalendar[FiscalYear] = 2021 )

Variance = [2021 Revenue] - [2020 Revenue]

WTDYOY% = DIVIDE ( [Variance], [2020 Revenue] ) 

That way the calculations will work at whatever level you are expanded to and with whatever slicers you apply.

I have attached my updated version of your file for you to look at.