Forum Discussion
Resolver IINeed Help - Calculating Sales when category is upgraded
Do you have a 'Category' table which defines which category a salesperson is in in each month? Something like this:
SalesPersonID Category Month 1 Silver Apr-2023 2 Silver Apr-2023 3 Silver Apr-2023 4 Silver Apr-2023 1 Gold May-2023 2 Gold May-2023 3 Gold May-2023 4 Silver May-2023 If you don't, then I think this is what you need to be able to make progress. I would have the [Month] column actually as a date (start of the month) as this will make it easier to join to your existing sales data table. Specifically, I would join in Power Query as follows:
Add a [Start of Month] column to your sales data
From your sales data, merge on both Sales.[SalesPersonID] <-> Category.[SalesPersonID] and Sales.[Start of Month] <-> Category.[Month]
Expand & just keep [category]
From your 'Category' table, you'll also need to generate a 'Category Changes' table. I would also do this in Power Query by:
Duplicating the 'Category' table
Renaming the [Category] column in this table to [Previous Category]
Adding a [Start of Previous Month] colmn
Joining this back to your catgory table on SalesPersonID <-> SalesPersonID and Month <-> [Start of Previous Month]
Filtering on [Category] <> [Previous Category]
Happy to expand on any of the above if you need more detail. I've just given an overview & am not sure how comfortable you are in Power Query already.
Hi,
Please find attached the solution file.
Hope this helps.
Thank you for your help, it was very sweet of you to do so. Highly appreciate.
Thank you
Resolver IISorry for the inconvenience. If it is taken time, pls ignore will tell the boss i cant do it. Thanks for your reply.
In the sales table, sales person category for that respective month is not mentioned, it is there in the category table. in the below examples
ID-1 in Apr-23 was in Gold Category, and ID-2 was in Silver category, but in June 2023, (Kindly refer the category table highligted in red) ID-1 and ID-2 gets upgraded to Diamond and Platinum Respectively. Wanted to know the result expected is
Result: In June-23 on Moving them to the next Category the visual i want is this
| Month | Category | Old Category | New Category | Tota Count | Total Sales |
| Jun-23 | Gold | Diamond | 1 | 110 | |
| Jun-23 | Silver | Platinum | 1 | 120 |
Ie actual sales of June-23 for ID-1 and ID-2, the number of sales people will be more, so wanted to know category wise count ( Of the changes) and total sales of those people for that month after moving them to the next category
Sales Table
| Sales ID | Month | Sales |
| ID-1 | Apr-23 | 10 |
| ID-2 | Apr-23 | 20 |
| ID-3 | Apr-23 | 30 |
| ID-4 | Apr-23 | 40 |
| ID-5 | Apr-23 | 50 |
| ID-1 | May-23 | 60 |
| ID-2 | May-23 | 70 |
| ID-3 | May-23 | 80 |
| ID-4 | May-23 | 90 |
| ID-5 | May-23 | 100 |
| ID-1 | Jun-23 | 110 |
| ID-2 | Jun-23 | 120 |
| ID-3 | Jun-23 | 130 |
| ID-4 | Jun-23 | 140 |
| ID-5 | Jun-23 | 150 |
Category table:
| Sales ID | Month | Category |
| ID-1 | Apr-23 | Gold |
| ID-2 | Apr-23 | Silver |
| ID-3 | Apr-23 | Bronze |
| ID-4 | Apr-23 | Platinum |
| ID-5 | Apr-23 | Diamond |
| ID-1 | May-23 | Gold |
| ID-2 | May-23 | Silver |
| ID-3 | May-23 | Bronze |
| ID-4 | May-23 | Platinum |
| ID-5 | May-23 | Diamond |
| ID-1 | Jun-23 | Diamond |
| ID-2 | Jun-23 | Platinum |
| ID-3 | Jun-23 | Bronze |
| ID-4 | Jun-23 | Platinum |
| ID-5 | Jun-23 | Diamond |
- santoshlearner2
Sales ID (This is sales table) Month Sales ID-1 Apr-23 10 ID-2 Apr-23 20 ID-3 Apr-23 30 ID-1 May-23 60 ID-2 May-23 70 ID-3 May-23 80 ID-1 Jun-23 110 ID-2 Jun-23 120 ID-3 Jun-23 130 Sales table above.
Category table
Sales ID Month Category ID-1 Apr-23 Gold ID-2 Apr-23 Silver ID-3 Apr-23 Platinum ID-1 May-23 Gold ID-2 May-23 Silver ID-3 May-23 Platinum ID-1 Jun-23 Diamond ID-2 Jun-23 Platinum ID-3 Jun-23 Platinum - santoshlearner2
Hi
Have sent the table in the previous reply.
Sorry for the inconvenience. If it is taken time, pls ignore will tell the boss i cant do it. Thanks for your reply.
In the sales table, sales person category for that respective month is not mentioned, it is there in the category table. in the below examples
ID-1 in Apr-23 was in Gold Category, and ID-2 was in Silver category, but in June 2023, (Kindly refer the category table highligted in red) ID-1 and ID-2 gets upgraded to Diamond and Platinum Respectively. Wanted to know the result expected is
Result: In June-23 on Moving them to the next Category the visual i want is this
Month Category Old Category New Category Tota Count Total Sales Jun-23 Gold Diamond 1 110 Jun-23 Silver Platinum 1 120 Ie actual sales of June-23 for ID-1 and ID-2, the number of sales people will be more, so wanted to know category wise count ( Of the changes) and total sales of those people for that month after moving them to the next category
Sales ID (This is sales table) Month Sales ID-1 Apr-23 10 ID-2 Apr-23 20 ID-3 Apr-23 30 ID-1 May-23 60 ID-2 May-23 70 ID-3 May-23 80 ID-1 Jun-23 110 ID-2 Jun-23 120 ID-3 Jun-23 130 Sales table above.
Category table
Sales ID Month Category ID-1 Apr-23 Gold ID-2 Apr-23 Silver ID-3 Apr-23 Platinum ID-1 May-23 Gold ID-2 May-23 Silver ID-3 May-23 Platinum ID-1 Jun-23 Diamond ID-2 Jun-23 Platinum ID-3 Jun-23 Platinum - Ashish_Mathur
Super User
Hi,
Please find attached the solution file.
Hope this helps.