Forum Discussion
Prepare Target source data for Sankey diagram
Hello,
I am trying to create a Sankey Diagram with the overview of changes over time in each Department over each year (or potentially a smaller period) and am looking for a way to transform the dataset that I am dealing with. However, the options that I have found online do not quite deal with the problem that I have.
My source table looks like this:
| ID | DEP | YEAR | Value | SNKID |
| 1 | X1 | 2019 | 1 | X1 2019 |
| 2 | X2 | 2019 | 1 | X2 2019 |
| 3 | X3 | 2019 | 1 | X3 2019 |
| 4 | X1 | 2019 | 1 | X1 2019 |
| 5 | X2 | 2019 | 1 | X2 2019 |
| 6 | X3 | 2019 | 1 | X3 2019 |
| 7 | X1 | 2019 | 1 | X1 2019 |
| 8 | X2 | 2019 | 1 | X2 2019 |
| 9 | X3 | 2019 | 1 | X3 2019 |
| 10 | X1 | 2019 | 1 | X1 2019 |
| 1 | X2 | 2020 | 1 | X2 2020 |
| 2 | X2 | 2020 | 1 | X2 2020 |
| 3 | X2 | 2020 | 1 | X2 2020 |
| 4 | X3 | 2020 | 1 | X3 2020 |
| 5 | X2 | 2020 | 1 | X2 2020 |
| 6 | X3 | 2020 | 1 | X3 2020 |
| 7 | X1 | 2020 | 1 | X1 2020 |
| 8 | X2 | 2020 | 1 | X2 2020 |
| 9 | X1 | 2020 | 1 | X1 2020 |
| 10 | X1 | 2020 | 1 | X1 2020 |
| 1 | X2 | 2021 | 1 | X2 2021 |
| 2 | X2 | 2021 | 1 | X2 2021 |
| 3 | X2 | 2021 | 1 | X2 2021 |
| 4 | X3 | 2021 | 1 | X3 2021 |
| 5 | X2 | 2021 | 1 | X2 2021 |
| 6 | X3 | 2021 | 1 | X3 2021 |
| 7 | X1 | 2021 | 1 | X1 2021 |
| 8 | X2 | 2021 | 1 | X2 2021 |
| 9 | X2 | 2021 | 1 | X2 2021 |
| 10 | X2 | 2021 | 1 | X2 2021 |
Ideally, the resulting table would be the following:
Where each row is a count of the path an ID took over the course of the time series, based on SNKID. IE as the graph shows no one who was in X2 left X2 over the course of the years.
For example: ID 1 went from being assigned X1 to being assigned X2 between 2019 and 2020, and is the only one who did so. Essentially, it is the same structure that as in this video:
https://youtu.be/AK2KsJm_r2o?t=411
| Source | Target | Value |
| X1 2019 | X1 2020 | 2 |
| X1 2019 | X2 2020 | 1 |
| X1 2019 | X3 2020 | 1 |
| X2 2019 | X2 2020 | 3 |
| X3 2019 | X1 2020 | 1 |
| X3 2019 | X2 2020 | 1 |
| X3 2019 | X3 2020 | 1 |
| X1 2020 | X1 2021 | 1 |
| X1 2020 | X2 2021 | 2 |
| X2 2020 | X1 2021 | 0 |
| X2 2020 | X2 2021 | 5 |
| X3 2020 | X3 2021 | 2 |
Screenshot version:
The goal is to the create a Sankey Diagram that would look like this: (and is a rendition of the table above)
Thank you in advance! If more information is needed or this topic already exists, let me know.
Hi YeltsinC ，
You can follow the below steps to get it, please find the details in the attachment.
1. Create a calculated column to get the target SNKID
Target = CALCULATE ( MIN ( 'Table'[SNKID] ), FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) && 'Table'[YEAR] > EARLIER ( 'Table'[YEAR] ) ) )2. Create a measure to get the sum of Value
NValue = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[Target] ) ) ) )3. Create a Sankey chart (Source: SNKID Destination: Target Weight: NValue)
Best Regards
3 Replies
I added a bit more details to show what I mean exactly, hope it helps?
Hi YeltsinC ，
You can follow the below steps to get it, please find the details in the attachment.
1. Create a calculated column to get the target SNKID
Target = CALCULATE ( MIN ( 'Table'[SNKID] ), FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) && 'Table'[YEAR] > EARLIER ( 'Table'[YEAR] ) ) )2. Create a measure to get the sum of Value
NValue = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[Target] ) ) ) )3. Create a Sankey chart (Source: SNKID Destination: Target Weight: NValue)
Best Regards
