Binning everything above a threshold together in a histogram

[samueldips62]

In the following histogram, what do I need to do to combine all values above 1000 to show as single bin at the far right.

This is the vega-lite json I have now:

{
“$schema”: “https://vega.github.io/schema/vega-lite/v5.json”,
“data”: { “name”: “dataset” },
“transform”: [
{
“filter”: “datum.Paid_Fare >= 0”
},
{
“filter”: “datum.Tickets_Used == 1”
}
],
“layer”: [
{
“mark”: “bar”,
“encoding”: {
“x”: {
“field”: “Paid_Fare”,
“type”: “quantitative”,
“bin”: {“maxbins”:10,“extent”:[0,1000]},
“axis”: {
“labelExpr”: “datum.value > 1000 ? ‘1000+’ : format(datum.value, ‘.0f’)”
}
},
“y”: {
“aggregate”: “count”,
“type”: “quantitative”,
“axis”: null
}
}
},
{
“mark”: {
“type”: “text”,
“align”: “center”,
“baseline”: “middle”,
“dx”: 0,
“dy”: -5
},
“encoding”: {
“text”: {
“aggregate”: “count”,
“type”: “quantitative”
},
“x”: {
“field”: “Paid_Fare”,
“type”: “quantitative”,
“bin”: {“maxbins”:10,“extent”:[0,1000]}
},
“y”: {
“aggregate”: “count”,
“type”: “quantitative”,
“axis”: null
}
}
}
]
}

[Greg]

Hi @samueldips62.

To help us further analyze your current state and visualize your issue, could you please provide as many as you can of:

• Your work-in-progress PBIX file, using sanitized data if necessary
  • here’s @BrianJ’s YouTube video on how to anonymize data in a Power BI file: https://www.youtube.com/watch?v=VmWD7Ayw_NI
• Your dataset as an Excel file (again, sanitized if necessary)
• A detailed mock-up (marked-up screenshot of the visual in question or Excel file) showing your desired outcome.

Also, if you provide DAX, Power Query, or Deneb JSON code in your post, please format it using the built-in formatter.

(From the sounds of it, you’ll need a composite visual (2 histograms side-by-side), but the wip PBIX file, data file, and mockup will provide more info.)

Greg

[samueldips62]

{
 "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
 "data": { "name": "dataset" },
 "transform": [
    {
      "filter": "datum.Room_Rate >= 0"
    }
 ],
 "layer": [
    {
      "mark": {
        "type": "bar",
        "color": "gray"
      },
      "encoding": {
        "x": {
          "field": "Room_Rate",
          "type": "quantitative",
          "bin": {"maxbins":10,"extent":[0,1000]},
          "axis": {
            "labelExpr": "datum.value > 1000 ? '1000+' : format(datum.value, '.0f')"
          }
        },
        "y": {
          "aggregate": "count",
          "type": "quantitative",
          "axis": null
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "align": "center",
        "baseline": "bottom",
        "dx": 0,
        "dy": -2,
        "color": "black"
      },
      "encoding": {
        "x": {
          "field": "Room_Rate",
          "type": "quantitative",
           "bin": {"maxbins":10,"extent":[0,1000]},
          "axis": {
            "title": "Room Rates"
          }
        },
        "y": {
          "aggregate": "count",
          "type": "quantitative",
          "axis": null
        },
        "text": {
          "aggregate": "count",
          "type": "quantitative",
          "format": ".0f"
        }
      }
    }
 ]
}

Here is a sample. You can see that the Room_Rate has values above 1000. Anyway for the histogram to combine every value above 1000 into a single bin. Obviously there are manual workarounds within powerbi but wondering if this is something that can be done in vegalite with the source being just the rates.
DummyData_Histogram.pbix (1.5 MB)

[Greg]

Hi @samueldips62.

After a quick search, I was unable to find out how to set custom bins in a Vega-Lite histogram. What you can do, however, as I thought, is to create a composite visual with horizontally concatenated visuals, in your case with the left one a mostly as-is histogram (just with a filter to under 1000), and a right one that is a standard bar chart (with a custom field setting the “value” to 1000+ and filtered to over 1000).

This generates quite disparate numbers for the Y-axis, so you’ll need to give some thought and adjust as necessary. Perhaps a mock-up in PowerPoint? Nevertheless, here’s my work-in-progress.

Hope it helps.
Greg
eDNA Forum - Deneb Histogram with Custom Bins.pbix (1.5 MB)

[samueldips62]

Thanks so much, this gets me well over 90 percent of the way to what I need.