Set a Sales Plan for a Person on a detailed data set

[untrue]

Hi everyone.

I am working with the Load Script and have the following problem

There is a detailed data set of sales numbers by sales person, clients, products.

Sales	Client	Product	Revenue
Sales 1	Client 1	Product1	1000
Sales 1	Client 1	Product 2	2000
Sales 1	Client 2	Product 1	1000
Sales 1	Client 2	Product 2	2000
Sales 1	Client 2	Product 3	3000

There is an inline input of sales target numbers for each sales person

Sales	Plan
Sales 1	10000
Sales 2	30000

I want to add this plan numbers to the first table 

I came up with using ApplyMap but it becomes as below which multiples the added Plan by the number of rows.

Sales	Client	Product	Revenue	Plan
Sales 1	Client 1	Product1	1000	10000
Sales 1	Client 1	Product 2	2000	10000
Sales 1	Client 2	Product 1	1000	10000
Sales 1	Client 2	Product 2	2000	10000
Sales 1	Client 2	Product 3	3000	10000

What is the best practice to solve this?

[rubenmarin · Accepted solution]

Hi, what you expect when a user selects a customer like Client 1? Plan for Sales 1 would by 10000 or 0?

If you expect to keep the 10000 the best option is to load Sales target as another table related with detailed data using Sales Field.

If you expect to show 0 as plan when someone selects a Client or a Product the you should Concatenate sales target to detail data

DetailData:
LOAD... //detail fields

Concatenate(DetailData)
LOAD ... //target fields

[Vegar]

I  would go for @rubenmarin  approach or reconsider if you need the data in the detail-data table or if you could leave it as a linked table that is associated on the field [Sales].

The two approaches will have different behaviour when you make selections on other dimensions such as the [Product] dimension.  

[untrue]

Hi @rubenmarin and thank you for your answer. The plan is set by Sales rep and Product levels. So when selecting the Client 1 it is not expected to show any plan. I guess concatenation makes sense. And then I should create separate Revenue measures which filter out the concatenated (plan) values to exclude it from total revenue sum calculations, correct?

[rubenmarin]

Hi, it's not needed becasue field names are different.

Revenue field will only be loaded from datail data, and Plan field only from plan data, so you can use Sum(Revenue) and it will not sum any of the plans. Sum(Plan) can be used to show Plan value.