top of page
Writer's pictureChris Barber

Sankey Diagram: Completing the dimension and fact input tables

Level

Sorting

From

To

Colour Code

Justification

1

1

B2B revenue

Revenue

1

left

1

2

B2C and other revenue

Revenue

1

left

2

1

Revenue

Gross margin

0

left

2

2

Revenue

Gross margin

0

left

3

1

Gross margin

EBITDA

2

left

3

2

Gross margin

OPEX

2

left

3

3

Cost of revenue

B2B costs

3

left

3

4

Cost of revenue

B2C costs

3

left

In this guide, we’ll walk through how to complete two essential input tables for the Sankey diagram Power BI visual:

  • Sankey Dimension

  • Sankey Fact

To proceed, you'll need a Sankey diagram (like the one created in my previous blog post):



Sanky Dimension

The first step is to complete all the levels and sorting permutations based on your Sankey diagram. Each level in the diagram will have a given number of sorting options. For example, at Level 1, there are two sorting options (1 and 2).


Step 1: Level and Sorting

Level

Sorting

1

1

1

2

2

1

2

2

3

1

3

2

3

3

3

4

In the example diagram, there are 29 unique sorting permutations in total:

  • 2 at Level 1

  • 2 at Level 2

  • 4 at Level 3

  • 7 at Level 4

  • 8 at Level 5

  • 6 at Level 6


By combining the level and sorting information, we can create a unique primary key (which we’ll generate using Power Query). This primary key will be used to link the dimension table to the fact table, helping us determine the corresponding values to display.

Level

Sorting

Sankey_Key

1

1

11

1

2

12

2

1

21

2

2

22

3

1

31

3

2

32

3

3

33

3

4

34


Step 2: "From" and "To" columns

Using the Sankey diagram requirements document, we can specify the "From" and "To" columns, which represent the flow of data between different categories. For instance, Level 1 Sorting 1 represents a flow from "B2B revenue" to "Revenue." These labels will appear in your final Sankey visualization.

Level

Sorting

From

To

1

1

B2B revenue

Revenue

1

2

B2C and other revenue

Revenue

2

1

Revenue

Gross margin

2

2

Revenue

Gross margin

3

1

Gross margin

EBITDA

3

2

Gross margin

OPEX

3

3

Cost of revenue

B2B costs

3

4

Cost of revenue

B2C costs

Step 3: Colour Code

Next, we need to assign a color code for each flow. Color coding helps visually differentiate various flows in the Sankey diagram. The rules are as follows:

  • Any flow from "Revenue" to another "Revenue" should have a color code of 1 (e.g., "B2B revenue" to "Revenue").

  • Any flow from "Revenue" to another category should have a color code of 0 (e.g., "Revenue" to "Gross Margin").

  • Any flow from a "Profit" line should have a color code of 2 (e.g., "Gross Margin" to "OPEX").

  • Flows from an "Expense" line should have a color code of 3 (e.g., "Cost of Revenue" to "B2B Costs").

Level

Sorting

From

To

Colour Code

1

1

B2B revenue

Revenue

1

1

2

B2C and other revenue

Revenue

1

2

1

Revenue

Gross margin

0

2

2

Revenue

Gross margin

0

3

1

Gross margin

EBITDA

2

3

2

Gross margin

OPEX

2

3

3

Cost of revenue

B2B costs

3

3

4

Cost of revenue

B2C costs

3

Step 4: Justification

You can configure text justification (left or right) in your input tables:

Level

Sorting

From

To

Colour Code

Justification

1

1

B2B revenue

Revenue

1

left

1

2

B2C and other revenue

Revenue

1

left

2

1

Revenue

Gross margin

0

left

2

2

Revenue

Gross margin

0

left

3

1

Gross margin

EBITDA

2

left

3

2

Gross margin

OPEX

2

left

3

3

Cost of revenue

B2B costs

3

left

3

4

Cost of revenue

B2C costs

3

left


Fact

In my book Income Statement Semantic Models, I explain how each line in the income statement is linked to an Income_Statement_Key. For example, "B2B revenue" corresponds to Income_Statement_Key = 2.

In the Sankey Fact table, you'll need to use the Sankey_Key (a combination of level and sorting) and link it to the relevant income statement lines to determine the values displayed. For instance, Sankey_Key 12 (B2C and Other Revenue) links to Income_Statement_Key 3 (B2C revenue) and 27 (Other income, net).

Sankey_Key

Income_Statement_Key

11

2

12

3

12

27

21

2

21

3

21

7

21

8

21

27

22

9

31

20

32

13

32

14

32

15

32

17

33

7

34

8

41

29

42

22

42

23

43

13

44

14

45

15

45

17

51

32

52

30

53

22

54

23

57

15

58

17


Conclusion

With both the dimension and fact tables completed, you can now explore the 4 different measurement components.

102 views0 comments

Comentários


bottom of page