how to include union of data in 1 visual

[Anonymous]

hello all, 

this might be quite simple but i need some help.

i have 2 tables of data:

1. Back Order value for each month for each product and customer: 

2. sales value for each month for each product

there could have SKU that appear in only 1st table but not 2nd table, or vice versa. (eg in above, SKU 1 has back order in Jan but no sales in Jan) 

in the visual (could be a simple line chart or matrics table), i want to show for each month, what is the back order and sales for each product. product that appear in either the back order table or the sales table need to be included in the table. 

Can someone explain how i can link to 2 tables and model this? 

i tried by either linking SKU, or SKU_Month in the 2 tables but seem not working.

SKU1, which only has back order in Jan but no sales, does not appear in the table, if i use the "month" from sales table as Rows.

[amitchandak]

@Anonymous 

You need to create common dimensions SKU and month/date and join with your table and then you can look data together by using these dimensions. 

Refer this guideline doc from Microsoft : https://docs.microsoft.com/en-us/power-bi/guidance/

[Anonymous]

 hi @amitchandak  do you mean have a field SKU&Month in each table, and then join the table?

i did this:

but the results are not i wanted:

the visual @Pragati11  share above is what i wanted. im just not sure how to easily create the 3rd table of "SKU Month Details" 

[Pragati11]

Hi @Anonymous ,

I have created a data model as follows using your 2 tables:

I have created an additional table - SKU Month Details.

This table has got a mapping between every SKU against every MONTH values. Data is shown below:

Then I created the following relationships:

1. SKU Month Details <--> SKU Sales ON "SKU No" (bi directional)
2. SKU Sales <--> SKU Back Order ON "SKU No" (bi directional)

Then I created a visual with details as follows:

On this visual I have moved columns from the tables as follows:

1. SKU No --> "SKU Month Details" table
2. Month --> "SKU Month Details" table
3. Back Order --> "SKU Back Order" table
4. Sales --> "SKU Sales" table

Hope this solution works, as it works at my end.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Anonymous]

hello @Pragati11  how to create the 3rd table "SKU Month Details" easily though? assuming if we have a lot of data points. 

[Pragati11]

Hi @Anonymous ,

I created this table in excel spreadsheet to have a mapping between Month and SKU No based on the sample data that you provided.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Anonymous]

If you make an effort to learn the correct data modeling techniques, you'll know how to do such standard things in no time.

Please watch these for a start (it'll show you how to solve many other problems as well):

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.youtube.com/watch?v=78d6mwR8GtA

Best

D