Help coloring Deneb heatmap

[Doug7983]

I’m new to Deneb and haven’t been able to figure out how to color this heatmap so that it is colored by row, or category, rather than as a whole:

I want the rows to be color independently of one another, like this:

This is my first forum post and I don't see a way to post a .pbix. Here is the Deneb specification using Vega Lite:

{
"data": {"name": "dataset"},
"mark": {
"type": "rect",
"stroke": "white",
"tooltip": true
},
"encoding": {
"y": {
"field": "Category",
"type": "nominal",
"title": "",
"axis": {
"domain": false,
"ticks": false,
"labels": true,
"labelAngle": 0,
"labelPadding": 5
}
},
"x": {
"field": "Date",
"type": "ordinal",
"title": "",
"timeUnit": "yearmonth",
"axis": {
"domain": false,
"ticks": false,
"labels": true,
"labelAngle": -90,
"orient": "top"
}
},
"tooltip": [
{
"field": "Category",
"type": "nominal"
},
{
"field": "Date",
"type": "ordinal"
},
{
"field": "Growth (MoYaM)",
"type": "quantitative",
"format": ".1%"
},
{
"field": "Growth (WDC)",
"type": "quantitative",
"format": ".1%"
}
],
"color": {
"aggregate": "max",
"field": "Growth (WDC)",
"type": "quantitative",
"title": "Growth (WDC)",
"scale": {
"scheme": "pbiColorDivergent"
},
"legend": {
"direction": "vertical",
"gradientLength": 120,
"format": ".1%"
}
}
}
}

Any help is greatly appreciated. Thanks.

[giammariam · Accepted solution]

@Doug7983, I see the issue. I had a rogue closed bracket in the data object that I missed. I deleted it and updated the above spec. Try it now.

  "data": {
    "name": "dataset"
    ] //<-- Should not be there
  }

[giammariam]

@Doug7983, no problem! Sorry it took so long for me to comprehend what you were after, but glad we got it in the end. Feel free to tag me in any future posts if you have any Deneb related questions. Always happy to get some vega/vega-lite practice in.

[giammariam]

@Doug7983, dang, that's no good. What does the date tooltip say for cells in this column? I can see if I can recreate it on my end

[Doug7983]

@giammariam It says Jan 01, 2023

[giammariam]

@Doug7983, I see it in my dataset as well. Do you have data for the months that are missing in your dataset? I do not in mine, and since we're using an ordinal scale, they won't show up without some additional transforms. It is possible to account for the months with no data, but I want to confirm that lines up with what you're seeing on your end as well.

[Doug7983]

@giammariam On closer inspection, the fiscal years are off a bit, which maybe is tied to the January problem? Jul 1, 2023 is actually FY24. 

The values for Jan 1 2023 actually seem to correspond to FY23, as they should. Does that help?

[giammariam · Accepted solution]

Another great catch @Doug7983. That's what I get for trying to rush through it 😆

gist

{
  "transform": [
    {"calculate": "(month(datum['Date'])+1)", "as": "monthNum"},
    {
      "calculate": "datum['monthNum'] + (datum['monthNum'] < 7 ? 6 : -6)",
      "as": "monthSort"
    },
    {
      "calculate": "'FY ' + toString(year(datum['Date']) + (datum['monthSort'] <= 6 ? +1 : 0))",
      "as": "FY"
    }
  ],
  "facet": {
    "column": {"field": "FY", "title": null, "header": {"labelFontSize": 13}}
  },
  "resolve": {"scale": {"x": "independent"}},
  "spacing": {"column": 5},
  "spec": {
    "width": {"step": 30},
    "mark": {"type": "rect", "stroke": "white", "tooltip": true},
    "encoding": {
      "x": {
        "field": "Date",
        "type": "ordinal",
        "title": null,
        "timeUnit": "month",
        "axis": {
          "domain": false,
          "ticks": false,
          "labels": true,
          "labelOpacity": 0.65,
          "orient": "top"
        },
        "sort": {"field": "monthSort"}
      },
      "y": {
        "field": "Category",
        "type": "nominal",
        "title": "",
        "axis": {
          "domain": false,
          "ticks": false,
          "labels": true,
          "labelOpacity": 0.65,
          "labelAngle": 0,
          "labelPadding": 5
        }
      },
      "tooltip": [
        {"field": "Category", "type": "nominal"},
        {"field": "Date", "type": "temporal"},
        {"field": "Growth (MoYaM)", "type": "quantitative", "format": ".1%"},
        {"field": "Growth (WDC)", "type": "quantitative", "format": ".1%"}
      ],
      "color": {
        "aggregate": "max",
        "field": "Growth (WDC)",
        "type": "quantitative",
        "title": "Growth (WDC)",
        "scale": {"domainMid": 0, "reverse": true, "scheme": "blueorange"},
        "legend": {
          "titleBaseline": "bottom",
          "direction": "vertical",
          "format": ".1%",
          "orient": "right"
        }
      }
    }
  },
  "data": {
    "name": "dataset"
  }
}

[Doug7983]

@giammariam You went way above and beyond. Thanks so much.

-Doug

[Doug7983]

@giammariam Also, just noticed there is no Jan in FY2021. It goes from Dec to Feb

[giammariam]

Hey @Doug7983, let me know if this is any closer. Note - you can eliminate the spacing between the FYs by setting the column spacing to -5.

Here's the gist.

Here's the spec:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "transform": [{"calculate": "'FY ' + year(datum['Date'])", "as": "FY"}],
  "facet": {
    "column": {"field": "FY", "title": null, "header": {"labelFontSize": 13}}
  },
  "resolve": {"scale": {"x": "independent"}},
  "spacing": {"column": 5},
  "spec": {
    "width": {"step": 30},
    "mark": {"type": "rect", "stroke": "white", "tooltip": true},
    "encoding": {
      "x": {
        "field": "Date",
        "type": "ordinal",
        "title": null,
        "timeUnit": "month",
        "axis": {
          "domain": false,
          "ticks": false,
          "labels": true,
          "labelOpacity": 0.65,
          "orient": "top"
        }
      },
      "y": {
        "field": "Category",
        "type": "nominal",
        "title": "",
        "axis": {
          "domain": false,
          "ticks": false,
          "labels": true,
          "labelOpacity": 0.65,
          "labelAngle": 0,
          "labelPadding": 5
        }
      },
      "tooltip": [
        {"field": "Category", "type": "nominal"},
        {"field": "Date", "type": "ordinal"},
        {"field": "Growth (MoYaM)", "type": "quantitative", "format": ".1%"},
        {"field": "Growth (WDC)", "type": "quantitative", "format": ".1%"}
      ],
      "color": {
        "aggregate": "max",
        "field": "Growth (WDC)",
        "type": "quantitative",
        "title": "Growth (WDC)",
        "scale": {"domainMid": 0, "reverse": true, "scheme": "blueorange"},
        "legend": {
          "titleBaseline": "bottom",
          "direction": "vertical",
          "format": ".1%",
          "orient": "right"
        }
      }
    }
  },
  "data": {
    "name": "dataset"
  }
}

[Doug7983]

Hey @giammariam. The screenshot looks extremely promising, but the spec is throwing this error message:

Invalid specification {}. Make sure the specification includes at least one of the following properties: "mark", "layer", "facet", "hconcat", "vconcat", "concat", or "repeat".

I'm not far enough down the learning path to troubleshoot it, but I wonder whether it may have something to do with this piece about "FY":

  "transform": [{"calculate": "'FY ' + year(datum['Date'])", "as": "FY"}],

  "facet": {

    "column": {"field": "FY", "title": null, "header": {"labelFontSize": 13}}

Thanks for your help. This looks like it could well work if we can get past that error.

[giammariam · Accepted solution]

@Doug7983, I see the issue. I had a rogue closed bracket in the data object that I missed. I deleted it and updated the above spec. Try it now.

  "data": {
    "name": "dataset"
    ] //<-- Should not be there
  }

[Doug7983]

@giammariam This is is exactly! Thank you so much.

Doug

[Doug7983]

@giammariam Just noticed one thing: My fiscal year starts in July, not January. So July 2023, for example, is in FY24.

[giammariam]

Hey @Doug7983, good catch, unfortunately I'm out of time at the moment, but will be able to get back to this later. If instead you want to create the FY calculation in DAX and then bring that field into Deneb, that may be a better option. If you go this route, be sure to call it "FY" (or update the spec with the measure/calculated column name that you choose).

Also, remove this line from the spec, otherwise it will overwrite your FY column:

 "transform": [{"calculate": "'FY ' + year(datum['Date'])", "as": "FY"}],

[Doug7983]

Totally get it, @giammariam. I'm grateful for your help. I've tried making changes myself, but the best I could get to was simply changing the "FY" in the spec to "CY". While accurate, it's not really what I need. 

My Date table has FiscalYear and FiscalMonth fields, but I couldn't get that approach to work, either.

[giammariam]

Try this:

{
  "transform": [
    {"calculate": "'FY '+ year(datum['Date'])", "as": "FY"},
    {"calculate": "(month(datum['Date'])+1)", "as": "monthNum"},
    {
      "calculate": "datum['monthNum'] + (datum['monthNum'] < 7 ? 6 : -6)",
      "as": "monthSort"
    },
    {
      "calculate": "'FY ' + toString(year(datum['Date']) + (datum['monthSort'] > 7 ? -1 : 0))",
      "as": "FY"
    }
  ],
  "facet": {
    "column": {"field": "FY", "title": null, "header": {"labelFontSize": 13}}
  },
  "resolve": {"scale": {"x": "independent"}},
  "spacing": {"column": 5},
  "spec": {
    "width": {"step": 30},
    "mark": {"type": "rect", "stroke": "white", "tooltip": true},
    "encoding": {
      "x": {
        "field": "Date",
        "type": "ordinal",
        "title": null,
        "timeUnit": "month",
        "axis": {
          "domain": false,
          "ticks": false,
          "labels": true,
          "labelOpacity": 0.65,
          "orient": "top"
        },
        "sort": {"field": "monthSort"}
      },
      "y": {
        "field": "Category",
        "type": "nominal",
        "title": "",
        "axis": {
          "domain": false,
          "ticks": false,
          "labels": true,
          "labelOpacity": 0.65,
          "labelAngle": 0,
          "labelPadding": 5
        }
      },
      "tooltip": [
        {"field": "Category", "type": "nominal"},
        {"field": "Date", "type": "temporal"},
        {"field": "Growth (MoYaM)", "type": "quantitative", "format": ".1%"},
        {"field": "Growth (WDC)", "type": "quantitative", "format": ".1%"}
      ],
      "color": {
        "aggregate": "max",
        "field": "Growth (WDC)",
        "type": "quantitative",
        "title": "Growth (WDC)",
        "scale": {"domainMid": 0, "reverse": true, "scheme": "blueorange"},
        "legend": {
          "titleBaseline": "bottom",
          "direction": "vertical",
          "format": ".1%",
          "orient": "right"
        }
      }
    }
  },
  "data": {
    "name": "dataset"
  }
}

gist 

[Doug7983]

@giammariam I've got a rogue January in FY23, other than that, perfect.

[giammariam]

Hey @Doug7983, I'm only providing the values in the values array in the gist for example purposes. You can see that in the embedded spec (not the gist) I left that array out. Deneb assumes that you have named your data "dataset", and will receive the values dynamically from Power BI.

In the example that I provided, each row is independent from one another color-wise and thus has its own legend. That's why the lightest color for one row has a different minimum input domain (lowest value) than that of another row. If you were to go to the gist and comment out the following line:

  "resolve": {"scale": {"color": "independent"}},

then the legend would no longer be independent for each row.

(color domains and legends are independent per row)

vs.

(color domain and legend are shared across entire dataset)

Forgive me, but I'm having trouble understanding. I'm confused about what you're trying to achieve because this:

My data has a range of -12.4% to 64.8%:

The deepest blue will always correspond to the highest value in the data set, regardless of row.

The deepest orange will always correspond to the lowest value in the data set, regardless of row.

The spectrum of color is applied such that a particular shade of blue, for example, will represent the same value, no matter where it appears. Same for shades of orange and everything in between. 

seems to conflict with:

I’m new to Deneb and haven’t been able to figure out how to color this heatmap so that it is colored by row, or category, rather than as a whole:

<.png>

I want the rows to be color independently

 
For example, if the deepest blue will always correspond to the highest value in the data set regardless of row, the heatmap would not be colored independently.

[Doug7983]

Hey @giammariam 

You're absolutely right. Those are conflicting statements. I think maybe the missing piece is describing how the orange-blue diverging color palette is applied? 

When I don't attempt to intervene in how Deneb applies pbiColorDivergent, Category 6 dominates the visual. 

But that is far different from the original Tableau chart:

i think the difference may lie in how the colors are being applied. Here is a screenshot from Tableau:

I’ve highlighted the dynamically created Start, End, and Center points. By default, the center of the spectrum is zero and the end colors are at equal distances from zero. Tableau assigns a lighter orange to -0.124 because the darkest orange would correspond to -0.648 – exactly opposite the maximum positive number. This tells the viewer that the lowest value, in orange, is not as far away from zero as the highest value.

I hope I haven't confused you further. I'm confused enough for both of us. Thanks for the help.

Doug

[giammariam]

Ah, this is very helpful! The midpoint of the input range is set to 0 in your Tableau screenshot. The scale in vega-lite is just taking into account the min/max values, so the midpoint here would be (max-min)/2. This would explain the color discrepancy. Let me play with it and see if I can get the scale to account for the 0 center threshold.