powerbi - How to display the absolute difference between two values on a dumbbell plot - Stack Overflow

I'm working on a dumbbell plot in Deneb/Power BI and I want to show the difference between each of the groups (absolute value) above the connecting line, like this:

I've tried to do this using a PBI measure that calculates the difference, but when I tried to implement it, each of the circles was labeled with the value, rather than the line labeled with a single value. So maybe it's best to do this calculation within Deneb? I'm just not sure how to do it.

Here's a link to the PBI file:

The code to generate the image below is:

{
  "$schema": ".json",
  "description": "Dumbbell chart with labels",
  "data":  {"name": "dataset"},
 
  "encoding": {
    "x": {
      "field": "Value",
      "type": "quantitative",
      "title": "Factor Value"//,
    },
    "y": {
      "field": "Categories",
      "type": "nominal",
      "title": null//,
    }
  },

  "layer": [
        {
      "mark": "line",
      "encoding": {
        "detail": {
          "field": "Categories",
          "type": "nominal"
        },
        "color": {"value": "gray"}
      }
    },

    {
      "mark": {
        "type": "point",
        "filled": true
      },
      "encoding": {
        "color": {
          "field": "Factor",
          "type": "ordinal",
           "legend": {
                 "title": null,
                 "offset": 0
            },
          "scale": {
            "domain": ["One", "Two"],
            "range": ["#6ba5cd", "#8e946a"]
          },
          "title": "Legend"
        },
        "size": {"value": 300},
        "opacity": {"value": 1}
      }
    }
  ]
}

I'm working on a dumbbell plot in Deneb/Power BI and I want to show the difference between each of the groups (absolute value) above the connecting line, like this:

I've tried to do this using a PBI measure that calculates the difference, but when I tried to implement it, each of the circles was labeled with the value, rather than the line labeled with a single value. So maybe it's best to do this calculation within Deneb? I'm just not sure how to do it.

Here's a link to the PBI file: https://drive.google.com/file/d/1_AG5jF6PV1eHc2A4QMSSk2iXm2TRZVc3/view?usp=drive_link

The code to generate the image below is:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "description": "Dumbbell chart with labels",
  "data":  {"name": "dataset"},
 
  "encoding": {
    "x": {
      "field": "Value",
      "type": "quantitative",
      "title": "Factor Value"//,
    },
    "y": {
      "field": "Categories",
      "type": "nominal",
      "title": null//,
    }
  },

  "layer": [
        {
      "mark": "line",
      "encoding": {
        "detail": {
          "field": "Categories",
          "type": "nominal"
        },
        "color": {"value": "gray"}
      }
    },

    {
      "mark": {
        "type": "point",
        "filled": true
      },
      "encoding": {
        "color": {
          "field": "Factor",
          "type": "ordinal",
           "legend": {
                 "title": null,
                 "offset": 0
            },
          "scale": {
            "domain": ["One", "Two"],
            "range": ["#6ba5cd", "#8e946a"]
          },
          "title": "Legend"
        },
        "size": {"value": 300},
        "opacity": {"value": 1}
      }
    }
  ]
}

• powerbi
• vega-lite
• vega
• deneb

Share Improve this question Follow edited Jan 6 at 23:13 jonrsharpe 122k3030 gold badges268268 silver badges476476 bronze badges asked Jan 6 at 22:29 JR87JR87 5344 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Alright, I was able to figure this out. Solution is in this file: https://drive.google.com/file/d/1pGR6s_Z7kAvCvCj7lKNHMIYpTDBmoj3j/view?usp=drive_link

And the code is here:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "description": "Dumbbell chart with labels",
  "data":  {"name": "dataset"},

  "encoding": {
    "x": {
      "field": "Value",
      "type": "quantitative",
      "title": "Factor Value"//,
    },
    "y": {
      "field": "Categories",
      "type": "nominal",
      "title": null//,
    }
  },
  
  "layer": [
        {
      "mark": "line",
      "encoding": {
        "detail": {
          "field": "Categories",
          "type": "nominal"
        },
        "color": {"value": "gray"}
      }
    },

    {
      "mark": {
        "type": "point",
        "filled": true
      },
      "encoding": {
        "color": {
          "field": "Factor",
          "type": "ordinal",
           "legend": {
                 "title": null,
                 "offset": 0
            },
          "scale": {
            "domain": ["One", "Two"],
            "range": ["#6ba5cd", "#8e946a"]
          },
          "title": "Legend"
        },
        "size": {"value": 300},
        "opacity": {"value": 1}
      }
    },
     {
      "mark": {
        "type": "text",
        "align": "center",
        "dy": -10,
        "fontSize": 12
      },
      "encoding": {
        "text": {
          "field": "Difference",
          "type": "quantitative",
          "format": ".2f"
        },
        "x": {
          "field": "Midpoint",
          "type": "quantitative"
        },
        "y": {
          "field": "Categories",
          "type": "nominal"
        },
        "color": {
          "value": "black"
        }
      },
      "transform": [
        {
          "aggregate": [
            {"op": "max", "field": "Value", "as": "Value_Two"},
            {"op": "min", "field": "Value", "as": "Value_One"}
          ],
          "groupby": ["Categories"]
        },
        {
          "calculate": "(datum.Value_Two + datum.Value_One) / 2",
          "as": "Midpoint"
        },
        {
          "calculate": "abs(datum.Value_Two - datum.Value_One)",
          "as": "Difference"
        }
      ]
    }
  ]
}