Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
.
Solved! Go to Solution.
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.
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.
User | Count |
---|---|
99 | |
87 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |