Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone, I have a data in below format
Store | week | sales |
X | 9 | 555 |
X | 8 | 647 |
X | 7 | 889 |
X | 6 | 846 |
X | 5 | 558 |
X | 4 | 993 |
Y | 9 | 736 |
Y | 8 | 746 |
Y | 7 | 927 |
Y | 6 | 601 |
Y | 5 | 509 |
Y | 4 | 589 |
Z | 9 | 964 |
Z | 8 | 578 |
Z | 7 | 833 |
Z | 6 | 992 |
Z | 5 | 558 |
Z | 4 | 739 |
and I want to create a summary table (using power BI table visual) as per blow
current week | Previous Avg | %diff | |
Store X | 555 | 786.60 | -29.4% |
Store Y | 736 | 674.40 | 9.1% |
Store Z | 964 | 740.00 | 30.3% |
How can I do this more efficiently so that each week I don't have to manually filter anything .e.g., if week 10 sales are added then current week should be moved to week 10 and all previous weeks should be included in Previous week avg.
How to include this automation in measure % diff and other columns where I am currently using filters
Your help will be appreciated.
Thanks.
Solved! Go to Solution.
Seems like:
Current Week Measure =
VAR __Store = MAX('Table'[Store])
VAR __MaxWeek = MAXX(FILTER('Table',[Store] = __Store),[week])
RETURN
MAXX(FILTER('Table',[Store] = __Store && [week] = __MaxWeek),[sales])
For previous average:
Previous Avg Measure =
VAR __Store = MAX('Table'[Store])
VAR __MaxWeek = MAXX(FILTER('Table',[Store] = __Store),[week])
RETURN
AVERAGEX(FILTER('Table',[Store] = __Store && [week] <> __MaxWeek),[sales])
For % diff:
%Diff Measure =
([Current Week Measure] - [Previous Avg Measure]) / [Previous Avg Measure]
For % diff:
%Diff Measure =
([Current Week Measure] - [Previous Avg Measure]) / [Previous Avg Measure]
For previous average:
Previous Avg Measure =
VAR __Store = MAX('Table'[Store])
VAR __MaxWeek = MAXX(FILTER('Table',[Store] = __Store),[week])
RETURN
AVERAGEX(FILTER('Table',[Store] = __Store && [week] <> __MaxWeek),[sales])
Seems like:
Current Week Measure =
VAR __Store = MAX('Table'[Store])
VAR __MaxWeek = MAXX(FILTER('Table',[Store] = __Store),[week])
RETURN
MAXX(FILTER('Table',[Store] = __Store && [week] = __MaxWeek),[sales])
Thanks Greg, worked like a charm 🙂
Assuming you are using just a numbered week rather than a date table, you could add a custom columns to your table to achieve this.
So create a custom column as:
Latest Week = MAX(Table[week])
then a second column to dictate if a line is in the current week or last week:
@Anonymous
Refer this file , How I creates Week , last/prior week using Rank. In case you have only one year data you can use Week
https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |