Multi Step Sankey Chart

Madraykin

Board Regular
Joined
Jan 4, 2012
Messages
56
Hi All,

So I'm usually posting on here about my Excel exploits, so something a little different today as I'm coming back to Power BI after a couple of years away.

I'm building a Sankey Chart which has 4 steps to visualise flow - there should be no difference in the start and end figures.

I'm working from three tables:

Table 1 - Source, Mid1, Count
Table 2 - Mid1, Mid2, Count
Table 3 - Mid2, Destination, Count

So it's Source to Destination going through various categories in stages Mid1 and Mid2

I initially used Union and Summarize to create a new table on which to develop the Sankey Chart - visually it worked, I have the 4 stages, but there is a fundamental drop-off. There are 33,000 records (and we end up with that figure at the Destination on the right of the Chart), but the Source (at the left of the Chart) has 99,000, so it is tripling what the original figure should be. I can also see stages from Mid1 and Mid2 in the Source 'column' on the left of the chart.

I've not built a Sankey in Power BI before so was researching how best to structure my data after failing miserably when going at it alone and various articles online seemed to suggest structuring my 'Step' data in different tables and then UNION/SUMMARIZE-ing it. If I do just 3 phases (ie Source, Mid1 and Destination) then I get 66,000 in the first 'Column'. I can't seem to get my head around how to achieve the UNION without duplicating records somehow.

So I guess the question is (being lost right now), can anyone suggest the best way to structure the data for what I'm trying to achieve?

Source has 18 categories
Mid1 has 9 categories
Mid2 has 7 categories
Destination has 3 categories

Obviously each of those 33k records can have any form of journey through these categories

Any help appreciated from you lovely guys, as always!

Mads
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top