How do I create Sankey Chart from two table data?

[pbsamar]

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

[mickey64 · Accepted solution]

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.

[PhilipTreacy]

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

[pbsamar]

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