How to calculate weekly sales amount ranking difference?

[stormzh]

Hi friend,

I have an example Excel spreadsheet containing records for my weekly product sale amount in different cities. It has four columns like the following. I loaded the Excel spreadsheet into PowerBI Desktop as my date source.

Sales City            Product ID       Sales Amount       Sales Week Ending Date

The weekly total sale amount for any certain product is the sum of the sales amount from all the involved cities.

On my report page, I have a slicer that allows me to select any single one of the week ending dates (for example, Apr 8, 2017, Apr 15, 2017, Apr 22, 2017 and etc.) from the list.

My question: How can I get a report showing the difference between the ranking of each product by their weekly total sale amount in the selected week with the ranking of the same product by their weekly total sale amount in the previous week? Please note that if any certain product does not have any sale amount record appearing in a certain week, then its ranking for that week should be considered one position behind the product having lowest ranking for that week.

For example, in the week ending on Apr 15, 2017, let’s assume that Product-A, Product-B and Product-C have sales amount records and their total weekly sale amount rankings for that week are 3, 1, and 2 respectively. In the previous (which ends on Apr 8, 2017), Product-B, Product-C, Product-D and Product-E have sales amount records and their total weekly sale amount rankings for that week are 4, 2, 3, 1, respectively.

If I select the “Apr 15, 2017” as the week ending date in my slicer, I’d like to get a list of ranking difference in descendant order as follow:

Product ID           Weekly Ranking Difference

Product-B               3                    --- (which is 4 minus 1)

Product-A               2                    --- (which is 5 minus 3)

Product-C               0                    --- (which is 2 minus 2)

Product-D               -1                    --- (which is 3 minus 4)

Product-E               -3                    --- (which is 1 minus 4)

I’d like to know how this scenario can be calculated and implemented in PowerBI. Thanks much in advance for any light you could shed on this.

Regards,

stormzh

[CheenuSing]

hi @stormzh

Can you provide some sample data with the pbix and put it in onedrive and share the link to formulate a solution.

Cheers

CheenuSing

[stormzh]

Hi CheenuSing,

Thanks for the reply. I am out of the city and currently unable to put a pbix file onto a share drive online. But I managed to put together a sample table which mimics the data I have so that you can easily copy and paste into an Excel file and get it loaded by PowerBI.

Here is the sample data records.

SalesCity	ProductID	SalesAmount	SalesWeekEndingDate
New York	Product-A	7	15/04/2017
Los Angeles	Product-A	12	15/04/2017
Chicago	Product-B	25	15/04/2017
New York	Product-B	23	15/04/2017
Houston	Product-C	5	15/04/2017
New York	Product-C	30	15/04/2017
Chicago	Product-C	8	15/04/2017
Chicago	Product-B	14	08/04/2017
New York	Product-C	32	08/04/2017
Chicago	Product-C	22	08/04/2017
New York	Product-D	17	08/04/2017
Los Angeles	Product-D	5	08/04/2017
Chicago	Product-D	10	08/04/2017
Houston	Product-D	8	08/04/2017
Los Angeles	Product-E	45	08/04/2017
Houston	Product-E	42	08/04/2017

For the current week 15/4/2017, Product-B ranks 1st by its total SalesAmount (48), Product-C ranks 2nd by its total SalesAmount (43) and Product-A ranks 3rd by its total SalesAmount (19).

For the previous week 8/4/2017, Product-E ranks 1st by its total SalesAmount (87), Product-C ranks 2nd by its total SalesAmount (54), Product-D ranks 3rd by its total SalesAmount (40) and Product-B ranks 4th by its total SalesAmount (14).

When calculating ranking difference, for Product-A its ranking in the current week is 3 and its ranking in the previous week is 5 (because Product-A did not have any sales records in the previous week so that its ranking for that week was forced to set as one position behind the product (Product-B) having lowest ranking (4) for that week.). Therefore, the ranking difference for Product-A is 5-3=2.

Similarly, the calculated ranking difference for Product-B is 4-1=3. The calculated ranking difference for Product-C is 2-2=0. The calculated ranking difference for Product-D is 3-4=-1. The calculated ranking difference for Product-E is 1-4=-3.

In our report, there is a filter allowing me to select the week. If I choose the week “15/4/2017” from the filter, I’d like to get a list of ranking difference in descendant order as follow (and explained above):

ProductID           WeeklyRankingDifference

Product-B               3                   

Product-A               2                   

Product-C               0                    

Product-D               -1                   

Product-E               -3                   

Hope this makes it clear regarding what I would like to achieve in this scenario. Thanks a lot!

Best Regards,

stormzh

[v-caliao-msft]

@stormzh,

because Product-A did not have any sales records in the previous week so that its ranking for that week was forced to set as one position behind the product (Product-B) having lowest ranking (4) for that week

This requirement is hard to achievement. Suppose both product A and Product B dit not have any sales records in the previous week, which product is forced to set as 4 and which product is forced to set as 5?

The sample test below set the previous rank as 0 if the product did not have any sales records in previous week.
Rank = RANKX(FILTER(ALL(Table1),Table1[SalesWeekEndingDate]=EARLIER(Table1[SalesWeekEndingDate])),CALCULATE(SUM(Table1[SalesAmount]),ALLEXCEPT(Table1,Table1[SalesWeekEndingDate],Table1[ProductID])),,ASC,Dense)
WeekNumber = WEEKNUM(Table1[SalesWeekEndingDate])
WeeklyRankingDifference = Table1[Rank]-LOOKUPVALUE(Table1[Rank],Table1[WeekNumber],Table1[WeekNumber]-1,Table1[ProductID],Table1[ProductID])

Regards,

Charlie Liao