Joining does not work properly

[dananjayaprasad]

Hello, 

I have 4 tables and I join them by product SKU, but when I try to create a table using Sale_AC and Inv to it does not work the way I want it. I want to see each place quantity, but it gives me total quantity for all location. I could not figure it out. Can anybody please tell me what’s wrong with it.

 

Thank you so much 

[dananjayaprasad]

Here is the link for sample pbix with dataset and join that I did

[parry2k]

what result you are expecting? It is working as expected. it is showing Inv.Quantity for sky, you are showing line items from sales table and it is repeating inv. quantity, either you sum up your sales quantity (one row for each sku) and then it will show you correct result.

I don't see any issue, it is just how you are visualizing your data.

If you remove store name, all is fine. 

[dananjayaprasad]

@parry2k

Thank you for quick respond. 

I want to see each store that particular SKU (I have a filter for SKU to see different product and I also have a store filter too.) Performance. I want to see how many quantities sold, sales (From Sales_Ac), any transfers, on order and Inv.Qunatity which means stock on  hand for each store from Int table.

I have an internal tool to validate.  Per this SKU (BPCBDP000176) We have total inventory of 79 (whole company). I am looking get something similar like this. Please see below table

Store Name	Products Name	SKU	Sold Quantity	Sales	On Transfer	On Order	Inv.Quantity
203 Tyler	Adopted Wrap iPhone 6/6s - OLIVE/GUNMETAL	BPCBDP000176	1	18.98			2
301 Evansville	Adopted Wrap iPhone 6/6s - OLIVE/GUNMETAL	BPCBDP000176	1	18.98			10
314 Chattanooga	Adopted Wrap iPhone 6/6s - OLIVE/GUNMETAL	BPCBDP000176	2	37.96			5
410 Bend	Adopted Wrap iPhone 6/6s - OLIVE/GUNMETAL	BPCBDP000176	1	18.98			3
702 Athens	Adopted Wrap iPhone 6/6s - OLIVE/GUNMETAL	BPCBDP000176	1	18.98			0
706 West Cobb - Marietta (WC)	Adopted Wrap iPhone 6/6s - OLIVE/GUNMETAL	BPCBDP000176	1	18.98			1
708 Norcross	Adopted Wrap iPhone 6/6s - OLIVE/GUNMETAL	BPCBDP000176	2	37.96			1
713 St. Simons	Adopted Wrap iPhone 6/6s - OLIVE/GUNMETAL	BPCBDP000176	1	18.98			8
--- so and so
---
						Total	79

I change all my feild to sum, but still no change. Any idea why is that. I thought something wrong with Join. 

Thank you so much 

[parry2k]

If you look at your int table, it has only one row for SKU BPCBDP000176 with inv. quantity 79. if you want break down by store then you need to store invt. quantity by store in this table and that is what is missing.

I'm not sure your sure in your table below where did you get the break down of invt. quantity by store and sku. 

[dananjayaprasad]

@parry2k

Thank you for the respond. That might be the reason, but when I add store to the  Int table I cannot join with Sales table due duplicate values.  Do you creating another table for sku and join everything to sku will work for that?

Thank you so much 

[parry2k]

create a calculate column for key by concatenating both store number  + sku in both Int and sales table and then set relation on that new calculated column, it will give you what you need.

[dananjayaprasad]

@parry2k

Hello, 

I am so sorry to keep bothering you.  I did what exactly you said, and It gives me the same error, please see the below pictures. Do I need create another table or what will be the best solutions? 

Thank you so much 

[parry2k]

well as message say invt table has duplicate values or there is null value. check you inventory table and value in PK

[dananjayaprasad]

@parry2k

Hello, 

I found what wrong with it. Yes, it has duplicates, I think I have to create a sub table. I am not sure how to achieve that. I put another detail post with example. You can see on this below link. Again, thank you so much your time and help. I appreciate it.

https://community.powerbi.com/t5/Desktop/How-to-create-a-sub-table-like-sub-query-on-SQL-in-Power-BI...

[parry2k]

why your inventory table will have duplicate records, seems like there is dataset challenge than powerbi. You can surely have another table to join these together which is easy to do but I would recommend look at your invt table and make sure that you expect duplicate rows in it.

[dananjayaprasad]

@parry2k

I do nto have a controll on their databases. I have a sql server analysis connection with them. Any idea How I create a sub table on using my inventory table. So I can join with other table and start working on my Week on Had report. I just want to get the store name, sku and how many transfer, on stock and on order for that store.

[parry2k]

does the duplicate rows have same values?

[dananjayaprasad]

@parry2k

Hi, 

I just check it with sample data. It looks like it. 

Thank you 

[parry2k]

if that is the case, you can remove duplicate rows in query editory and that will give the unique value for each store and sku , and it will solve all the issues.

[dananjayaprasad]

@parry2k

Thank you but in the worst case scenario (for data integrity), is there any way we can write a DAX or some other method for creating a subtable by store, and sku , for that sku how many on order, on transfer, and on stock without repeating, because I only test sample data. Its huge database (Inventory), so I am kind of worrying about it, so is there any way we can do it. 

Thank you 

[parry2k]

just add a step to remove duplicate, we can always revert it and see if rest everything works.