Prepare Target source data for Sankey diagram

[YeltsinC]

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. 

[v-yiruan-msft · Accepted solution]

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

[Pragati11]

Hi @YeltsinC ,

You definitely need to add more details on the logic behind calculating your 2nd table with SOURCE/TARGET using your original table.

[YeltsinC]

I added a bit more details to show what I mean exactly, hope it helps?