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 | 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.
Comments