Sales targets including two different tables data with alternation option

[Anonymous]

Hey everyone, 

I have a question regarding data structure I am trying to create:

My data model looks like this:

1 Sales fact table: including Barcode(product's key), Quantity, Brand, Category, Industry, Sub-industry

2. Industry dim: sub-industry (key), industry

3. Products Database: Barcode, Brand, Category,  Description.

relationship as you probably already understand:   Sales-Industry (many to one)  (using sub-industry mutual field)

                                                                                  Sales-Products (many to one) (using barcode mutual field)

Now, I have a table I want to add to my model which is Sales Target representing this month sales targets.  The table includes the following fields:

Brand, Category, Industry, Sub Industry, Fiscal Target [Dollars].

My problem is that I would like to connect the sales target table so it will perfectly synchronize with both products and industry tables (since I created a dynamic X axis that will switch between industries/sub-industries and categories/brands).

I have thought about duplicating the sales targets table and connect it separately to each of the dimensions, however, it will double the targets and create doubled false targets...

I can't connect it to the sales fact table either because then I will have to choose between industries and products.

what are my options here? I want to be able to represent the sales targets in both aspects (product aspect and industry aspect) with accurate numbers.

Thanks a lot in advance!

OBAO

[v-lili6-msft]

hi, @Anonymous 

IF Sales Target table is data table, you could create a relationship with both products and industry tables.

Then fields from Target table must be aggregated.

If Sales Target table is dim table, You couldn't connect the sales target table so it will perfectly synchronize with both products and industry tables.

here is a similar post for you refer to:

https://community.powerbi.com/t5/Desktop/Bi-directional-cross-filtering-ambiguity/td-p/119890

(since I created a dynamic X axis that will switch between industries/sub-industries and categories/brands).

 what is your expected output dynamic x axis, could you share some simple sample data? We may try another way for it.

Best Regards,

Lin

[Anonymous]

Hey @v-lili6-msft thanks for your reply,

I am not sure if this is the solution, so let me try to be more informative:

This is my data model diagram as it is right now, the table and connections painted in red represent the sales targets table I am trying to combine in the data model.

I created the Slicer Table containing all the sub-industries and brands including a calculated measure so that I can create dynamic X axis using a slicer.

I want to get Sales targets by Brand/Sub-industry distribution according to the slicer selection.

I hope I made sense and you understood where I'm coming from.

Thanks again, I hope this is solvable. 

OBAO