Forum Discussion
How do I create Sankey Chart from two table data?
We have following two tables "Shop Transaction" and "Organization Tree".
Use PowerBI desktop to create a Sankey chart using these data.
Note that:
- When a shop is added or a shop changes district, the start date changes in "Organization Tree".
- Organization Tree’ has a hierarchy of Region> District> Shop. The Region is the root of the tree.
Shop Transaction
Organization Tree
Output Sankey data format
Output Sanky format
Step 0: I use these DATA Tables below. (I add some dammy data to 'Shop Transactions' table.)
Step 1: I add a column to these 2 tables each.
Step 2: I merge 2 tables.
- Before -
- After -
Step 3: I sort 'Shop Transactions' table with 'shop no' column and 'date'column.
Step 4: I fill down 'Parent' column.
3 Replies
Step 0: I use these DATA Tables below. (I add some dammy data to 'Shop Transactions' table.)
Step 1: I add a column to these 2 tables each.
Step 2: I merge 2 tables.
- Before -
- After -
Step 3: I sort 'Shop Transactions' table with 'shop no' column and 'date'column.
Step 4: I fill down 'Parent' column.
Hi pbsamar
Based on your desired result, it looks like a Decomposition Tree is more what you are after rather than a Sankey Diagram?
Either way, there's no way to relate the Shops in the first table to the Districts. How do you know which District the Shop1 and Shop21's belong to?
Regards
Phil
Hi PhilipTreacy
Based on 'date' from "Shop Transactions" and 'start date' from "Organization Tree" , a 'region' column need to be
created, which should look like :
date shop no check sale amount Region 2-Jun-24 shop1 1 10 district1 3-Jun-24 shop1 2 20 district1 4-Jun-24 shop1 3 30 district1 5-Jun-24 shop1 4 40 district2 6-Jun-24 shop1 5 50 district2 7-Jun-24 shop1 6 60 district2 2-Jun-24 shop2 1 10 district1 4-Jun-24 shop2 3 30 district1 5-Jun-24 shop2 4 40 district1 6-Jun-24 shop2 5 50 district1 7-Jun-24 shop2 6 60 district1 2-Jun-24 shop21 1 1 district2 3-Jun-24 shop21 2 2 district2 4-Jun-24 shop21 3 3 district2 5-Jun-24 shop21 4 4 district1 6-Jun-24 shop21 5 5 district1 7-Jun-24 shop21 6 6 district1 3-Jun-24 shop22 2 20 district2 4-Jun-24 shop22 3 30 district2 5-Jun-24 shop22 4 40 district2 6-Jun-24 shop22 5 50 district2 7-Jun-24 shop22 6 60 district2 Regards,
pbsamar
