Forum Discussion
Resolver IINeed Help - Calculating Sales when category is upgraded
I am a learner, learning from all of you wonderful people. I am stuck with a problem tried all the ways but cannot get it.
I have 4 categories of salespeople Ranked as per the highest
1) Diamond(Top Category)
2)) Gold (2nd)
3) Silver (3rd)
4) Bronze (4th)
Have sales figures likes this
Category / Sub Category/ Sales ID / Sales Person.
Wanted to know if there are 3 people in Silver category, in April 23, the supervisor moved them on May 23 to Gold then what was the incremental / lower business done.
Sales ID is Unique, Out of all people in silver category, 3 people in April 23 figures were like this
ID 1 – 100
ID 2- 240
ID 3- 124
If in May 23 since their boss moved them to upper category for eg gold their figures become like this
ID 1 – 120
ID 2- 250
ID 3- 140
Incremental nos is 46 with count 3, similary even after moving them up if they don’t done anything when what is the cumulative figures.
Thanks every one
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
12 Replies
whitchResolver I
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.
santoshlearner2It was very sweet of you to reply. I Have the following tables
Table One: known as Category Table; Which has Sales ID, Month, and the category. In this table the category names are mentioned like gold, silver, bronze,
Table 2; Known as Sales Table
Has the sales data like Sales ID, Month, Sales Figures, but does not have category.
Now the Problem i am facing is, Sales people who are say in the lower category and in any given month they get upgraded to the next level or higher level, wanted to know the count of those people who are upgraded , on upgrading them what is the sales numbers,
Pls note the sales table does not have the category, it is in the category table, both have the month, / Sales ID,
Hope i explained correctly. Thanks for your time
- whitch
Yes, generating a 'Category Changes' table, as outlined in my previous response, would solve both your problems.
- santoshlearner2
Thank you for your help, it was very sweet of you to do so. Highly appreciate.
Thank you
- whitch
By Category.[SalesPersonID], I mean the [SalesPersonID] column in the 'Category' table.
- Ashish_Mathur
Super User
Hi,
Share some data (in a format that can be pasted in an MS Excel file) and show the expected result very clearly.
- santoshlearner2
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 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
- santoshlearner2
Thank you for your help, it was very sweet of you to do so. Highly appreciate.
Thank you