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 all,
I'm quite new to the whole Power BI thing and I'm trying to achieve all my reporting needs 🙂
I have a table with the daily sales coming from several shops (sorry for the long table)
What I would like to achieve is to have the best and worst selling shop in the last week (data gets updated every monday morning) and the amount of sales for that shop(s)
In the example the best shop in the last week would be ROMA (PORTA DI ROMA) with a total of 36 sales and the worst shop would be ARESE (MI) with 5 sales.
Thanks a lot!
Date | Shop | Sales | Week |
05/10/2020 | ROMA (PORTA DI ROMA) | 3 | 41 |
09/10/2020 | ROMA (PORTA DI ROMA) | 3 | 41 |
09/10/2020 | ORIO AL SERIO (BG) | 4 | 41 |
10/10/2020 | ROMA (PORTA DI ROMA) | 13 | 41 |
10/10/2020 | ORIO AL SERIO (BG) | 15 | 41 |
11/10/2020 | ORIO AL SERIO (BG) | 11 | 41 |
11/10/2020 | ROMA (PORTA DI ROMA) | 16 | 41 |
16/10/2020 | ORIO AL SERIO (BG) | 3 | 42 |
17/10/2020 | ORIO AL SERIO (BG) | 8 | 42 |
18/10/2020 | ORIO AL SERIO (BG) | 12 | 42 |
16/10/2020 | ARESE (MI) | 20 | 42 |
17/10/2020 | ARESE (MI) | 31 | 42 |
23/10/2020 | ROMA (PORTA DI ROMA) | 12 | 43 |
23/10/2020 | ORIO AL SERIO (BG) | 14 | 43 |
23/10/2020 | ARESE (MI) | 5 | 43 |
24/10/2020 | ROMA (PORTA DI ROMA) | 12 | 43 |
25/10/2020 | ROMA (PORTA DI ROMA) | 12 | 43 |
@andrearuggeri , Try measures like
//assumes sales measure
Shop Rank desc = RANKX(all(Table[Shop]),[Sales])
Shop Rank asc= RANKX(all(Table[Shop]),[Sales],,asc)
Rank top 1 bottom 1 = sumx(filter(VALUES(Table[Shop]),[Shop Rank desc ]=1 || [Shop Rank asc] =1 ),[Sales] )
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
Not sure I completely got it.
I did take a look to the RANKX thing but if I'm not wrong the solution you provided is not aggregating the sales per week, so the rank is per date...
I need to determine for each week which is the shop with the max total sales, then I need it to know for the latest week.
EDIT: I will edit the original post since I realize there's a missing part, sorry (
Thanks a lot
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |