top of page
Writer's pictureChris Barber

Sankey Diagram: Data Modelling, DAX, and Visualisation

Now that we have the inputs ready, there are four key steps to build the solution:

  1. Power Query

  2. Modelling

  3. DAX

  4. Visualization

Let's dive into each step in detail.


Power Query

In this step, we will bring in three input tables from previous blog posts:

  • Sankey Dimension

  • Sankey Fact

  • Sankey Measures


Importing Sankey Fact

The Sankey Fact is straightforward to import through Power Query, with no transformations required except to make sure the data types are integers.


Sankey Dimension

For Sankey Dimension, we need to create an additional column, Sankey_Key, by combining the "Level" and "Sorting" columns. The Power Query code for this is shown below:



Sankey Measure

In the Sankey Measures table, two additional columns need to be calculated: Sankey Code and Sankey View Full. These are created by combining existing columns. The Power Query code for these transformations is as follows:


Modelling

Once your data is loaded and transformed in Power Query, it’s time to model the tables.

Set all columns to Don’t Summarize in the model, as none of these columns should be counted or summed. This ensures the integrity of your Sankey visual.


DAX  (Data Analysis Expressions)

Here, we will define the key measures and calculated columns using DAX.


Sankey Arrow Up and Down

You can create visual indicators for the Sankey arrows using Unicode characters:



Sankey Colour Rules

This rule determines the colors for your Sankey nodes, by taking the maximum of the Colour Rules Component Code:



Scenario Selected

This DAX formula captures the value of the selected Comparison Component:



Sankey Value

The Sankley Value measure calculates the required actual value for each Sankey Code. This is achieved by creating a virtual relationship between the Sankey Fact and the Layout tables. The formula then switches out variables to reflect the selected Sankey Code:




Scenario Value

The Scenario Value measure calculates the required scenario value for each Sankey Code. This is achieved by creating a virtual relationship between the Sankey Fact and the Layout tables. The formula then switches out variables to reflect the selected Sankey Code:





Visualisation

For the visualizations, we will use Deneb. The key attributes and measures needed for the Deneb visual are as follows:


Below is the complete Vega code for the Sankey chart visualization in Deneb:

{
  // Description of the chart
  "description": "Income Statement Sankey Chart",
  // Chart dimensions
  "width": 1250,
  "height": 620,
  // Chart padding
  "padding": {
    "bottom": 0,
    "left": 0,
    "right": 0,
    "top": 0
  },
  // Signals are variables that can be used throughout the specification
  "signals": [
    {
      "name": "standardGap",
      "value": 20,
      "description": "Gap as a percentage of full domain"
    },
    {
      "name": "base",
      "value": "center",
      "description": "How to stack (center or zero)"
    }
  ],

    

  // Data sources and transformations
  "data": [
    // Primary dataset
    {"name": "dataset"},
    // Create stacks from the dataset
    {
      "name": "stacks",
      "source": "dataset",
      "transform": [
        // Filter out records where 'From' is null
        {
          "type": "filter",
          "expr": "datum['From'] != null"
        },
        // Create an array ['From', 'To'] and store it in 'end'
        {
          "type": "formula",
          "as": "end",
          "expr": "['From','To']"
        },
        // Create an array with actual 'From' and 'To' values
        {
          "type": "formula",
          "as": "name",
          "expr": "[ datum['From'],datum['To']]"
        },
        // Project specific fields to include in the data
        {
          "type": "project",
          "fields": [
            "end",
            "name",
            "Sankey Value__formatted",
            "Sankey Scenario__formatted",
            "Sankey Value",
            "Sankey Scenario"
          ]
        },
        // Flatten the arrays in 'end' and 'name' to create individual records
        {
          "type": "flatten",
          "fields": ["end", "name"]
        },
        // Lookup additional attributes from 'dataset' based on 'From' field
        {
          "type": "lookup",
          "from": "dataset",
          "key": "From",
          "values": [
            "Level",
            "Sorting",
            "Level and Sorting",
            "Additional Gap",
            "Justification",
            "Sankey Arrow Up",
            "Sankey Arrow Down",
            "Scenario selected",
            "Colour",
            "Colour Code",
            "Sankey Colour Rules__formatted"
          ],
          "fields": ["name"],
          "as": [
            "Level",
            "Sorting",
            "Level and Sorting",
            "gap",
            "Justification",
            "Sankey Arrow Up",
            "Sankey Arrow Down",
            "Scenario selected",
            "Colour",
            "Colour Code",
            "Sankey Colour Rules__formatted"
          ]
        },
        // Aggregate data based on 'end' and 'name' fields
        {
          "type": "aggregate",
          "fields": [
            "Sankey Value__formatted",
            "Sankey Scenario__formatted",
            "Sankey Value",
            "Sankey Scenario",
            "Level",
            "Sorting",
            "Level and Sorting",
            "gap",
            "Justification",
            "Sankey Arrow Up",
            "Sankey Arrow Down",
            "Scenario selected",
            "Colour",
            "Colour Code",
            "Sankey Colour Rules__formatted"
          ],
          "groupby": ["end", "name"],
          "ops": [
            "sum", // Sum numerical values
            "sum",
            "sum",
            "sum",
            "max", // Use maximum for categorical values
            "max",
            "max",
            "max",
            "max",
            "max",
            "max",
            "max",
            "max",
            "min",
            "max"
          ],
          "as": [
            "Sankey Value__formatted",
            "Sankey Scenario__formatted",
            "Sankey Value",
            "Sankey Scenario",
            "Level",
            "Sorting",
            "Level and Sorting",
            "gap",
            "Justification",
            "Sankey Arrow Up",
            "Sankey Arrow Down",
            "Scenario selected",
            "Colour",
            "Colour Code",
            "Sankey Colour Rules__formatted"
          ]
        },
        // Aggregate data based on 'name' field
        {
          "type": "aggregate",
          "fields": [
            "Sankey Value__formatted",
            "Sankey Scenario__formatted",
            "Sankey Value",
            "Sankey Scenario",
            "Level",
            "Sorting",
            "Level and Sorting",
            "gap",
            "Justification",
            "Sankey Arrow Up",
            "Sankey Arrow Down",
            "Scenario selected",
            "Colour",
            "Colour Code",
            "Sankey Colour Rules__formatted"
          ],
          "groupby": ["name"],
          "ops": [
            "max",
            "max",
            "max",
            "max",
            "max",
            "max",
            "max",
            "max",
            "max",
            "max",
            "max",
            "max",
            "max",
            "max",
            "max"
          ],
          "as": [
            "Sankey Value__formatted",
            "Sankey Scenario__formatted",
            "Sankey Value",
            "Sankey Scenario",
            "Level",
            "Sorting",
            "Level and Sorting",
            "gap",
            "Justification",
            "Sankey Arrow Up",
            "Sankey Arrow Down",
            "Scenario selected",
            "Colour",
            "Colour Code",
            "Sankey Colour Rules__formatted"
          ]
        },
        // Ensure 'gap' has a default value of 0 if undefined
        {
          "type": "formula",
          "as": "gap",
          "expr": "datum.gap?datum.gap:0"
        }
      ]
    },
    // Calculate maximum value for scaling purposes
    {
      "name": "maxValue",
      "source": ["stacks"],
      "transform": [
        {
          "type": "aggregate",
          "fields": [
            "Sankey Value__formatted"
          ],
          "groupby": ["Level"],
          "ops": ["sum"],
          "as": [
            "Sankey Value__formatted"
          ]
        },
        {
          "type": "aggregate",
          "fields": [
            "Sankey Value__formatted"
          ],
          "ops": ["max"],
          "as": [
            "Sankey Value__formatted"
          ]
        }
      ]
    },



    
 
    // Prepare data for plotting the stacks
    {
      "name": "plottedStacks",
      "source": ["stacks"],
      "transform": [
        // Calculate spacer based on maximum value and gaps
        {
          "type": "formula",
          "as": "spacer",
          "expr": " (data('maxValue')[0]['Sankey Value__formatted']/100)*(standardGap+datum.gap)"
        },
        // Define types for stacking
        {
          "type": "formula",
          "as": "type",
          "expr": "['data','spacer']"
        },
        // Combine 'Sankey Value' and 'spacer' into an array
        {
          "type": "formula",
          "as": "spacedValue",
          "expr": "[datum['Sankey Value__formatted'],datum.spacer]"
        },
        // Flatten arrays to create individual records
        {
          "type": "flatten",
          "fields": [
            "type",
            "spacedValue"
          ]
        },
        // Stack the values based on 'Level' and 'Sorting'
        {
          "type": "stack",
          "groupby": ["Level"],
          "sort": {
            "field": "Sorting",
            "order": "descending"
          },
          "field": "spacedValue",
          "offset": {"signal": "base"}
        },
        // Calculate center position for labels
        {
          "type": "formula",
          "expr": "((datum['Sankey Value__formatted'])/2)+datum.y0",
          "as": "yc"
        }
      ]
    },
    // Finalize the table with calculated values
    {
      "name": "finalTable",
      "source": ["plottedStacks"],
      "transform": [
        // Filter to include only 'data' type records
        {
          "type": "filter",
          "expr": "datum.type == 'data'"
        },
        // Calculate variance percentage between 'Sankey Value' and 'Sankey Scenario'
        {
          "type": "formula",
          "as": "sankey value variance calculated",
          "expr": " abs((round(( datum['Sankey Scenario'] /datum['Sankey Value'] -1) *10000))/100) "
        },
        // Determine if the variance indicates an increase or decrease
        {
          "type": "formula",
          "as": "sankey increase or decrease",
          "expr": "if(datum['Sankey Value'] > datum['Sankey Scenario'],datum['Sankey Arrow Up'],datum['Sankey Arrow Down']) "
        },
        // Determine color code based on variance
        {
          "type": "formula",
          "as": "Colour Code Variance",
          "expr": "if(if(datum['Colour Code'] == 3,  datum['Sankey Scenario'] - datum['Sankey Value'] , datum['Sankey Value'] - datum['Sankey Scenario'])>0,2,3) "
        }
      ]
    },





    // Define color ranges for different categories
    {
      "name": "sankeyrange",
      "source": ["finalTable"],
      "transform": [
        {
          "type": "aggregate",
          "key": "Level and Sorting",
          "fields": ["Colour"],
          "ops": ["max"],
          "as": ["Colour Range"]
        }
      ]
    },
    // Separate data into positives
    {
      "name": "Positives",
      "source": ["finalTable"],
      "transform": [
        {
          "type": "filter",
          "expr": "if(datum['Sankey Colour Rules__formatted'] ==2,  datum['Colour Code Variance'] == 2,  datum['Colour Code'] == 2)"
        }
      ]
    },
    // Separate data into negatives
    {
      "name": "Negatives",
      "source": ["finalTable"],
      "transform": [
        {
          "type": "filter",
          "expr": "if(datum['Sankey Colour Rules__formatted'] ==2,  datum['Colour Code Variance'] == 3,  datum['Colour Code'] == 3)"
        }
      ]
    },
    // Separate data into neutral
    {
      "name": "Neutral",
      "source": ["finalTable"],
      "transform": [
        {
          "type": "filter",
          "expr": "if(datum['Sankey Colour Rules__formatted'] ==2,  datum['Colour Code Variance'] == 1,  datum['Colour Code'] == 1)"
        }
      ]
    },
    // Data for midpoint neutral
    {
      "name": "MidPointNeutral",
      "source": ["finalTable"],
      "transform": [
        {
          "type": "filter",
          "expr": "if(datum['Sankey Colour Rules__formatted'] ==1, datum['Colour Code'] == 0, datum['Colour Code'] == 100)"
        }
      ]
    },





    
   
    // Prepare links between nodes
    {
      "name": "linkTable",
      "source": ["dataset"],
      "transform": [
        // Filter out records where 'From' is null
        {
          "type": "filter",
          "expr": "datum['From'] != null"
        },
        // Lookup color codes from 'finalTable' for 'To' field
        {
          "type": "lookup",
          "from": "finalTable",
          "key": "name",
          "values": [
            "Colour Code",
            "Colour Code Variance"
          ],
          "fields": ["To"],
          "as": [
            "Colour Code To",
            "Color Code Variance To"
          ]
        },
        // Lookup source stack positions
        {
          "type": "lookup",
          "from": "finalTable",
          "key": "name",
          "values": [
            "y0",
            "y1",
            "Level",
            "Sorting"
          ],
          "fields": ["From"],
          "as": [
            "sourceStacky0",
            "sourceStacky1",
            "sourceStack",
            "sourceSort"
          ]
        },
        // Lookup destination stack positions
        {
          "type": "lookup",
          "from": "finalTable",
          "key": "name",
          "values": [
            "y0",
            "y1",
            "Level",
            "Sorting"
          ],
          "fields": ["To"],
          "as": [
            "destinationStacky0",
            "destinationStacky1",
            "destinationStack",
            "destinationSort"
          ]
        },
        // Stack the source links
        {
          "type": "stack",
          "groupby": ["From"],
          "sort": {
            "field": "destinationSort",
            "order": "descending"
          },
          "field": "Sankey Value__formatted",
          "offset": "zero",
          "as": ["syi0", "syi1"]
        },
        // Calculate starting positions for the links
        {
          "type": "formula",
          "expr": "datum.syi0+datum.sourceStacky0",
          "as": "sy0"
        },
        {
          "type": "formula",
          "expr": "datum.sy0+datum['Sankey Value__formatted']",
          "as": "sy1"
        },
        // Stack the destination links
        {
          "type": "stack",
          "groupby": ["To"],
          "sort": {
            "field": "sourceSort",
            "order": "descending"
          },
          "field": "Sankey Value__formatted",
          "offset": "zero",
          "as": ["dyi0", "dyi1"]
        },
        // Calculate ending positions for the links
        {
          "type": "formula",
          "expr": "datum.dyi0+datum.destinationStacky0",
          "as": "dy0"
        },
        {
          "type": "formula",
          "expr": "datum.dy0+datum['Sankey Value__formatted']",
          "as": "dy1"
        },
        // Calculate center positions for the source and destination
        {
          "type": "formula",
          "expr": "((datum['Sankey Value__formatted'])/2)+datum.sy0",
          "as": "syc"
        },
        {
          "type": "formula",
          "expr": "((datum['Sankey Value__formatted'])/2)+datum.dy0",
          "as": "dyc"
        },
        // Generate path for the links
        {
          "type": "linkpath",
          "orient": "horizontal",
          "shape": "diagonal",
          "sourceY": {
            "expr": "scale('y', datum.syc)"
          },
          "sourceX": {
            "expr": "scale('x', toNumber(  datum.sourceStack))+ bandwidth('x')"
          },
          "targetY": {
            "expr": "scale('y', datum.dyc)"
          },
          "targetX": {
            "expr": "scale('x', datum.destinationStack)"
          }
        },
        // Calculate stroke width for the links
        {
          "type": "formula",
          "expr": "range('y')[0]-scale('y', datum['Sankey Value__formatted'])",
          "as": "strokeWidth"
        }
      ]

        }, 
        
        
        
        
        
        
        
        
        
        
        
        
        
        
    
   
    // Create subsets of links based on color codes
    {
      "name": "linkTableFrom",
      "source": ["linkTable"],
      "transform": [
        {
          "type": "filter",
          "expr": "datum['Colour Code'] == 1"
        }
      ]
    },
    // Links for positive midpoint transitions
    {
      "name": "linkTableMidPointPositives",
      "source": ["linkTable"],
      "transform": [
        {
          "type": "filter",
          "expr": "if(datum['Sankey Colour Rules'] ==1, datum['Colour Code'] == 0 && datum['Colour Code To'] == 2,   datum['Colour Code'] == 0 && datum['Color Code Variance To'] == 2)  "
        }
      ]
    },
    // Links for negative midpoint transitions
    {
      "name": "linkTableMidPointNegatives",
      "source": ["linkTable"],
      "transform": [
        {
          "type": "filter",
          "expr": "if(datum['Sankey Colour Rules'] ==1,  datum['Colour Code'] == 0 && datum['Colour Code To'] == 3 ,   datum['Colour Code'] == 0 && datum['Color Code Variance To'] == 3) "
        }
      ]
    },

    
    {
      "name": "linkTableTo",
      "source": ["linkTable"],
      "transform": [
        {
          "type": "filter",
          "expr": "datum['Colour Code'] !== 1 && datum['Colour Code'] !== 0 "
        }
      ]
    }
    
    
    
     ],
  // Define scales for positioning and coloring
  "scales": [
    // X-axis scale for levels
    {
      "name": "x",
      "type": "band",
      "range": "width",
      "domain": {
        "data": "finalTable",
        "field": "Level"
      },
      "paddingInner": 0.88
    },
    // Y-axis scale for values
    {
      "name": "y",
      "type": "linear",
      "range": "height",
      "domain": {
        "data": "finalTable",
        "field": "y1"
      },
      "reverse": false
    },
    // Color scale for neutral nodes
    {
      "name": "color1",
      "type": "ordinal",
      "domain": {
        "data": "Neutral",
        "field": "name"
      },
      "range": ["#808080"]
    },
    // Color scale for positive nodes
    {
      "name": "color2",
      "type": "ordinal",
      "domain": {
        "data": "Positives",
        "field": "name"
      },
      "range": ["#3BB143"]
    },
    // Color scale for negative nodes
    {
      "name": "color3",
      "type": "ordinal",
      "domain": {
        "data": "Negatives",
        "field": "name"
      },
      "range": ["#FF0000"]
    },
    // Grayscale color scheme for other nodes
    {
      "name": "color4",
      "type": "ordinal",
      "range": {"scheme": "greys"},
      "domain": {
        "data": "finalTable",
        "field": "name"
      }
    },
    // Color scale for midpoint neutral nodes
    {
      "name": "color5",
      "type": "ordinal",
      "domain": {
        "data": "MidPointNeutral",
        "field": "name"
      },
      "range": ["#808080"]
    },
    // Additional color scales (if needed)
    {
      "name": "color6",
      "type": "ordinal",
      "domain": {
        "data": "finalTable",
        "field": "name"
      },
      "range": ["#3BB143"]
    },
    {
      "name": "color7",
      "type": "ordinal",
      "domain": {
        "data": "finalTable",
        "field": "name"
      },
      "range": ["#FF0000"]
    }
  ],

    
    
    
    
    
    
    
    

  



"marks": [
    // Draw rectangles for neutral nodes
    {
      "type": "rect",
      "from": {"data": "finalTable"},
      "encode": {
        "update": {
          "x": {
            "scale": "x",
            "field": "Level"
          },
          "width": {
            "scale": "x",
            "band": 1
          },
          "y": {
            "scale": "y",
            "field": "y0"
          },
          "y2": {
            "scale": "y",
            "field": "y1"
          },
          "fill": {
            "scale": "color1",
            "field": "name"
          },
          "fillOpacity": {
            "value": 0.75
          },
          "strokeWidth": {"value": 0},
          "stroke": {
            "scale": "color1",
            "field": "name"
          }
        },
        // Define hover effects
        "hover": {
          "tooltip": {
            "signal": "{'Name':datum.name, 'Value':datum['Sankey Value__formatted'] + 'm'}"
          },
          "fillOpacity": {"value": 1}
        }
      }
    },
    // Draw rectangles for positive nodes
    {
      "type": "rect",
      "from": {"data": "finalTable"},
      "encode": {
        "update": {
          // Similar properties as above, but using 'color2' scale
          "x": {
            "scale": "x",
            "field": "Level"
          },
          "width": {
            "scale": "x",
            "band": 1
          },
          "y": {
            "scale": "y",
            "field": "y0"
          },
          "y2": {
            "scale": "y",
            "field": "y1"
          },
          "fill": {
            "scale": "color2",
            "field": "name"
          },
          "fillOpacity": {
            "value": 0.75
          },
          "strokeWidth": {"value": 0},
          "stroke": {
            "scale": "color2",
            "field": "name"
          }
        },
        "hover": {
          "tooltip": {
            "signal": "{'Name':datum.name, 'Value':datum['Sankey Value__formatted'] + 'm'}"
          },
          "fillOpacity": {"value": 1}
        }
      }
    },
    // Draw rectangles for negative nodes
    {
      "type": "rect",
      "from": {"data": "finalTable"},
      "encode": {
        "update": {
          // Similar properties as above, but using 'color3' scale
          "x": {
            "scale": "x",
            "field": "Level"
          },
          "width": {
            "scale": "x",
            "band": 1
          },
          "y": {
            "scale": "y",
            "field": "y0"
          },
          "y2": {
            "scale": "y",
            "field": "y1"
          },
          "fill": {
            "scale": "color3",
            "field": "name"
          },
          "fillOpacity": {
            "value": 0.75
          },
          "strokeWidth": {"value": 0},
          "stroke": {
            "scale": "color3",
            "field": "name"
          }
        },
        "hover": {
          "tooltip": {
            "signal": "{'Name':datum.name, 'Value':datum['Sankey Value__formatted'] + 'm'}"
          },
          "fillOpacity": {"value": 1}
        }
      }
    },
    // Draw rectangles for midpoint neutral nodes
    {
      "type": "rect",
      "from": {"data": "finalTable"},
      "encode": {
        "update": {
          // Similar properties as above, but using 'color5' scale
          "x": {
            "scale": "x",
            "field": "Level"
          },
          "width": {
            "scale": "x",
            "band": 1
          },
          "y": {
            "scale": "y",
            "field": "y0"
          },
          "y2": {
            "scale": "y",
            "field": "y1"
          },
          "fill": {
            "scale": "color5",
            "field": "name"
          },
          "fillOpacity": {
            "value": 0.75
          },
          "strokeWidth": {"value": 0},
          "stroke": {
            "scale": "color5",
            "field": "name"
          }
        },
        "hover": {
          "tooltip": {
            "signal": "{'Name':datum.name, 'Value':datum['Sankey Value__formatted'] + 'm'}"
          },
          "fillOpacity": {"value": 1}
        }
      }
    },


    
    












    {
      "type": "path",
      "name": "linksneutralTo",
      "from": {"data": "linkTableTo"},
      "clip": true,
      "encode": {
        "update": {
          "strokeWidth": {
            "field": "strokeWidth"
          },
          "path": {"field": "path"},
          "strokeOpacity": {
            "signal": "0.3"
          },
          "stroke": {
            "field": "To",
            "scale": "color1"
          }
        },
        "hover": {
          "strokeOpacity": {"value": 1},
          "tooltip": {
            "signal": "{'From':datum['From'],'To':datum['To'], 'Value':format(datum['Sankey Value__formatted'], '$') + 'm'}"
          }
        }
      }
    },
    
    
        {
      "type": "path",
      "name": "linksneutralFrom",
      "from": {"data": "linkTableFrom"},
      "clip": true,
      "encode": {
        "update": {
          "strokeWidth": {
            "field": "strokeWidth"
          },
          "path": {"field": "path"},
          "strokeOpacity": {
            "signal": "0.3"
          },
          "stroke": {
            "field": "From",
            "scale": "color1"
          }
        },
        "hover": {
          "strokeOpacity": {"value": 1},
          "tooltip": {
            "signal": "{'From':datum['From'],'To':datum['To'], 'Value':format(datum['Sankey Value__formatted'], '$') + 'm'}"
          }
        }
      }
    },
    
    
    
    
    
    
    
        
    {
      "type": "path",
      "name": "linkspositiveMidPoint",
      "from": {"data": "linkTableMidPointPositives"},
      "clip": true,
      "encode": {
        "update": {
          "strokeWidth": {
            "field": "strokeWidth"
          },
          "path": {"field": "path"},
          "strokeOpacity": {
            "signal": "0.3"
          },
          "stroke": {
            "field": "From",
            "scale": "color6"
          }
        },
        "hover": {
          "strokeOpacity": {"value": 1},
          "tooltip": {
            "signal": "{'From':datum['From'],'To':datum['To'], 'Value':format(datum['Sankey Value__formatted'], '$') + 'm'}"
          }
        }
      }
    },
    
    
    
        {
      "type": "path",
      "name": "linksnegativeMidPoint",
      "from": {"data": "linkTableMidPointNegatives"},
      "clip": true,
      "encode": {
        "update": {
          "strokeWidth": {
            "field": "strokeWidth"
          },
          "path": {"field": "path"},
          "strokeOpacity": {
            "signal": "0.3"
          },
          "stroke": {
            "field": "From",
            "scale": "color7"
          }
        },
        "hover": {
          "strokeOpacity": {"value": 1},
          "tooltip": {
            "signal": "{'From':datum['From'],'To':datum['To'], 'Value':format(datum['Sankey Value__formatted'], '$') + 'm'}"
          }
        }
      }
    },
    
    
    
    {
      "type": "path",
      "name": "linkspositiveFrom",
      "from": {"data": "linkTableFrom"},
      "clip": true,
      "encode": {
        "update": {
          "strokeWidth": {
            "field": "strokeWidth"
          },
          "path": {"field": "path"},
          "strokeOpacity": {
            "signal": "0.3"
          },
          "stroke": {
            "field": "From",
            "scale": "color2"
          }
        },
        "hover": {
          "strokeOpacity": {"value": 1},
          "tooltip": {
            "signal": "{'From':datum['From'],'To':datum['To'], 'Value':format(datum['Sankey Value__formatted'], '$') + 'm'}"
          }
        }
      }
    },
    
    

    
        
    {
      "type": "path",
      "name": "linkspositiveTo",
      "from": {"data": "linkTableTo"},
      "clip": true,
      "encode": {
        "update": {
          "strokeWidth": {
            "field": "strokeWidth"
          },
          "path": {"field": "path"},
          "strokeOpacity": {
            "signal": "0.3"
          },
          "stroke": {
            "field": "To",
            "scale": "color2"
          }
        },
        "hover": {
          "strokeOpacity": {"value": 1},
          "tooltip": {
            "signal": "{'From':datum['From'],'To':datum['To'], 'Value':format(datum['Sankey Value__formatted'], '$') + 'm'}"
          }
        }
      }
    },
    
    
    
        
    
    {
      "type": "path",
      "name": "linksnegativeFrom",
      "from": {"data": "linkTableFrom"},
      "clip": true,
      "encode": {
        "update": {
          "strokeWidth": {
            "field": "strokeWidth"
          },
          "path": {"field": "path"},
          "strokeOpacity": {
            "signal": "0.3"
          },
          "stroke": {
            "field": "From",
            "scale": "color3"
          }
        },
        "hover": {
          "strokeOpacity": {"value": 1},
          "tooltip": {
            "signal": "{'From':datum['From'],'To':datum['To'], 'Value':format(datum['Sankey Value__formatted'], '$') + 'm'}"
          }
        }
      }
    },
    
    
    
        {
      "type": "path",
      "name": "linksnegativeTo",
      "from": {"data": "linkTableTo"},
      "clip": true,
      "encode": {
        "update": {
          "strokeWidth": {
            "field": "strokeWidth"
          },
          "path": {"field": "path"},
          "strokeOpacity": {
            "signal": "0.3"
          },
          "stroke": {
            "field": "To",
            "scale": "color3"
          }
        },
        "hover": {
          "strokeOpacity": {"value": 1},
          "tooltip": {
            "signal": "{'From':datum['From'],'To':datum['To'], 'Value':format(datum['Sankey Value__formatted'], '$') + 'm'}"
          }
        }
      }
    },

    
    
    
    
    
    {
      "type": "group",
      "name": "labelText",
      "zindex": 1,
      "from": {
        "facet": {
          "data": "finalTable",
          "name": "labelFacet",
          "groupby": [
            "name",
            "Level",
            "yc",
            "Sankey Value__formatted",
            "Justification"
          ]
        }
      },
      "clip": false,
      "encode": {
        "update": {
          "strokeWidth": {"value": 1},
          "stroke": {"value": "red"},
          "x": {
            "signal": "datum['Justification']=='left'?scale('x', datum['Level'])-8 : scale('x', datum['Level']) + (bandwidth('x')) +8"
          },
          "yc": {
            "scale": "y",
            "signal": "datum.yc"
          },
          "width": {"signal": "0"},
          "height": {"signal": "0"},
          "fillOpacity": {
            "signal": "0.1"
          }
        }
      },
      "marks": [
        {
          "type": "text",
          "name": "heading",
          "from": {
            "data": "labelFacet"
          },
          "encode": {
            "update": {
              "x": {"value": 0},
              "y": {"value": -12},
              "text": {"field": "name"},
              "align": {
                "signal": "datum['Justification']=='left'?'right':'left'"
              },
              "fontWeight": {
                "value": "normal"
              }
            }
          }
        },
        {
          "type": "text",
          "name": "amount",
          "from": {
            "data": "labelFacet"
          },
          "encode": {
            "update": {
              "x": {"value": 0},
              "y": {"value": 6},
              "text": {
                "signal": "format(datum['Sankey Value__formatted'], '$') + 'm' "
              },
              "align": {
                "signal": "datum['Justification']=='left'?'right':'left'"
              }
            }
          }
        },
        {
          "type": "text",
          "name": "second amount",
          "from": {
            "data": "labelFacet"
          },
          "encode": {
            "update": {
              "x": {"value": 0},
              "y": {"value": 24},
              "text": {
                "signal": "datum['sankey increase or decrease'] + datum['sankey value variance calculated']+ '% ' + datum['Scenario selected']  "
              },
              "align": {
                "signal": "datum['Justification']=='left'?'right':'left'"
              }
            }
          }
        }
      ]
    },
    {
      "type": "rect",
      "from": {"data": "labelText"},
      "encode": {
        "update": {
          "x": {
            "field": "bounds.x1",
            "offset": -2
          },
          "x2": {
            "field": "bounds.x2",
            "offset": 2
          },
          "y": {
            "field": "bounds.y1",
            "offset": -2
          },
          "y2": {
            "field": "bounds.y2",
            "offset": 2
          },
          "fill": {"value": "white"},
          "opacity": {"value": 0.8},
          "cornerRadius": {"value": 4}
        }
      }
    }
  ],
  "config": {
    "view": {"stroke": "transparent"},
    "text": {
      "fontSize": 11,
      "fill": "#333333"
    }
  }
}

This code defines the structure, style, and layout of the Sankey visual, using the attributes and measures calculated in the previous steps.


Conclusion

In this five-part blog series, we've walked through the entire process of building a Sankey visualization in Power BI. From why you'd build a sankey diagram to creating interactive visual elements using Deneb, I hope you find this blog series helpful.

160 views0 comments

Recent Posts

See All

Comments


bottom of page