Best and worst shop per week

[andrearuggeri]

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

[amitchandak]

@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...

[andrearuggeri]

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