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 IIIt 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
Resolver IYes, 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