Forum Discussion
Linking two table data into filterable report
Hello,
I have two tables. One table is sales table with customers:
Date\Client name\product\price\qty
Second table is returns data:
Date\Client name\product\failure name\qty
I would like to make powerbi page where i could show customers return % with filter. Anyone could help me to point correct direction?
7 Replies
You should move the Client Master table along with these two tables. Join on client name/id (Unique Column).
Then you will be able to create a formula across table. And you can create a filter or slicer on top of that.
Hey Blitzkrieg,
Ideally you would require an orderID or similar to be able to identify each unique order. This orderID would also be available within the returns table. You can then use this OrderID to link the 2 tables. I would suggest the layout being something like so:
OrderID\Date\Client name\product\price\qty
ReturnID\OrderID\Date\Client name\product\failure name\qty
Once an ID has been created you can create a relationship between the 2 tables via the OrderID. You could then do a count of the Orders received vs Returns received as a % of Returns.
Cheers,
Wayne
Hello
Yes that a way. I now linked two tables with Customer name as these are same for both tables. Question is now how do i match dates on both tables. For example i have sales for customer A 10 in january and 20 february. And for customer A i have 5 returns in january and 2 in february.
RMA % i calculate today by counting client named rows on each table and then dividing rma with sales data but the % is not correct and when i use rma date it does not check sales dates.
Hi Anonymous
This is the problem with linking via a name or similar as they're not unique. I would strongly recommend adding in a unique orderID and a unique returnID.
However, if you're unable to change what you could do here is concatenate the 2 columns; name and date. This will create a unique ID so long as the user doesn't order multiple times per day.
The above will still only work if you have some 'order data' within the return table i.e. an order date. If not it wouldn't be possible to link the order with the return in any way. You would simply just have to count the amount of orders for the users name and count the number of returns for the users name. This way you could get a return % in general, but you would not be able to tell exactly which orders were fulfilled.
Cheers,
Wayne
