Need Help - Calculating Sales when category is upgraded

[santoshlearner2]

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

[whitch · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the solution file.

Hope this helps.

[santoshlearner2 · Accepted solution]

Thank you for your help, it was very sweet of you to do so.  Highly appreciate.

Thank you

[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

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the solution file.

Hope this helps.

[Ashish_Mathur]

Hi,

Share some data (in a format that can be pasted in an MS Excel file) and show the expected result very clearly.

[whitch]

By Category.[SalesPersonID], I mean the [SalesPersonID] column in the 'Category' table.

[whitch · Accepted solution]

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.

[santoshlearner2]

Thank you for your help, it was very sweet of you to do so.  Highly appreciate.

Thank you

[santoshlearner2]

It 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]

Sorry to bother you i am not getting this part.  In my sales table category is not there so how do i merge this. My Query marked in bold.. It it is taking your time, you may ignore the question. Thanks for your assistance

Your reply: 

Add a [Start of Month] column to your sales data

Month Column is there in the Sales data

From your sales data, merge on both Sales.[SalesPersonID] <-> Category.[SalesPersonID] and Sales.[Start of Month] <-> Category.[Month]

Expand & just keep [category]

I have to merge 

Sales.[SalesPersonID] <-> Category.[SalesPersonID

In my Sales table i have the sales id, but i don thave category in it, How to i get the category in this, ie from category table for that respective date  how to i plot the respective month 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]

2) and Sales.[Start of Month] <-> Category.[Month]

In the sales table i have the month but not the category