Target line based on criteria

[Anonymous]

Hi,

I have a pretty simple sales scenario, where products are sold on a date basis for a brand and in a region.

Next to that I have a table with targets for the combination of a month, brand and region.

I would like to present the sales per date or month with the target line.

The target line should be adjusted if I filter the data to only showing one or a few brands and one or a few regions.

I am not sure how to link up the data and make this happen. Who can help me with a simple demo or some instructions on how to approach this?

Many thanks in advance!

[v-easonf-msft]

Hi, @Anonymous 

Could you share your current sample file and expected result  for testing?

Best Regards,
Community Support Team _ Eason

[negi007]

@Anonymous 

Similar to your actuals, you can also have a target table where you will have target sales value for each product, each brand for each region. In the date column, you can have the first day of the month as a start date. In this way, you can use the date in the target table to arrive at monthly calculation. below could be the structure of the target table. You can link target table with date column and other product and region table as well

Product	Brand	Region	Target Sale	Date
Prod 1	Brand 1	North	200	1-Jan-20
Prod 1	Brand 2	North	100	1-Jan-20
Prod 1	Brand 3	North	300	1-Jan-20
Prod 2	Brand 1	West	300	1-Jan-20
Prod 2	Brand 2	West	200	1-Jan-20
Prod 2	Brand 3	West	100	1-Jan-20
Prod 1	Brand 1	North	200	1-Feb-20
Prod 1	Brand 2	North	100	1-Feb-20
Prod 1	Brand 3	North	300	1-Feb-20
Prod 2	Brand 1	West	300	1-Feb-20
Prod 2	Brand 2	West	200	1-Feb-20
Prod 2	Brand 3	West	100	1-Feb-20

[Anonymous]

@negi007 Not sure this will work 😞

I will basically have a many to many relationship now, right?

ie My brand3 will be many times in the fact table and many times in the target table....

Any ideas how to resolve?