topic Re: Help coloring Deneb heatmap in Custom Visuals Development Discussion

Help coloring Deneb heatmap

Doug7983 — Wed, 18 Oct 2023 14:13:15 GMT

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.

Re: Help coloring Deneb heatmap

giammariam — Wed, 18 Oct 2023 00:11:07 GMT

Hey @Doug7983. Welcome to the forums! That's awesome that you're diving into Deneb. Thank you for providing your spec and screenshots. Here's how to provide a .pbix, which would be very helpful. Alternatively, if you're able to provide a sanitized dataset that matches your schema, that will also work.

Happy to try to help once I have either the dataset or .pbix.

Re: Help coloring Deneb heatmap

Doug7983 — Wed, 18 Oct 2023 14:27:01 GMT

Thanks for the reply, @giammariam. Security policies prevent me from uploading a .pbix as described in your linked instructions, and character limitations prevent pasting the full file here. I'm attempting to paste in a partial data set.

Date	Category	Growth (WDC)	Growth (MoYaM)
11/1/21	Category 01	23.2%	29.7%
12/1/21	Category 01	11.9%	11.9%
1/1/22	Category 01	12.9%	18.9%
2/1/22	Category 01	9.1%	9.1%
3/1/22	Category 01	20.8%	20.8%
4/1/22	Category 01	29.3%	23.5%
5/1/22	Category 01	35.6%	42.7%
6/1/22	Category 01	27.6%	27.6%
7/1/22	Category 01	42.4%	13.9%
8/1/22	Category 01	27.7%	33.5%
9/1/22	Category 01	17.7%	23.6%
10/1/22	Category 01	18.7%	18.7%
11/1/22	Category 01	9.0%	9.0%
12/1/22	Category 01	14.4%	14.4%
1/1/23	Category 01	10.1%	10.1%
2/1/23	Category 01	8.5%	8.5%
3/1/23	Category 01	13.0%	13.0%
4/1/23	Category 01	6.0%	0.9%
5/1/23	Category 01	3.0%	13.3%
6/1/23	Category 01	6.9%	6.9%
7/1/23	Category 01	11.8%	11.8%
8/1/23	Category 01	13.8%	13.8%
9/1/23	Category 01	17.6%	12.0%
10/1/23	Category 01	14.5%	-61.8%
11/1/21	Category 03	15.2%	21.3%
12/1/21	Category 03	10.6%	10.6%
1/1/22	Category 03	14.2%	20.2%
2/1/22	Category 03	12.4%	12.4%
3/1/22	Category 03	12.2%	12.2%
4/1/22	Category 03	13.5%	8.3%
5/1/22	Category 03	15.5%	21.5%
6/1/22	Category 03	6.5%	6.5%
7/1/22	Category 03	17.9%	-5.7%
8/1/22	Category 03	7.7%	12.6%
9/1/22	Category 03	-1.7%	3.2%
10/1/22	Category 03	8.0%	8.0%
11/1/22	Category 03	12.3%	12.3%
12/1/22	Category 03	6.7%	6.7%
1/1/23	Category 03	4.0%	4.0%
2/1/23	Category 03	8.4%	8.4%
3/1/23	Category 03	18.8%	18.8%
4/1/23	Category 03	14.0%	8.6%
5/1/23	Category 03	5.2%	15.7%
6/1/23	Category 03	12.7%	12.7%
7/1/23	Category 03	8.7%	8.7%
8/1/23	Category 03	10.5%	10.5%
9/1/23	Category 03	5.8%	0.8%
10/1/23	Category 03	1.7%	-66.1%
11/1/21	Category 10	24.0%	30.5%
12/1/21	Category 10	15.8%	15.8%
1/1/22	Category 10	11.8%	17.7%
2/1/22	Category 10	11.3%	11.3%
3/1/22	Category 10	18.3%	18.3%
4/1/22	Category 10	21.6%	16.1%
5/1/22	Category 10	14.6%	20.6%
6/1/22	Category 10	12.5%	12.5%
7/1/22	Category 10	25.4%	0.3%
8/1/22	Category 10	8.7%	13.6%
9/1/22	Category 10	9.0%	14.4%
10/1/22	Category 10	12.6%	12.6%
11/1/22	Category 10	2.6%	2.6%
12/1/22	Category 10	0.5%	0.5%
1/1/23	Category 10	3.9%	3.9%
2/1/23	Category 10	0.1%	0.1%
3/1/23	Category 10	-8.4%	-8.4%
4/1/23	Category 10	-7.7%	-12.1%
5/1/23	Category 10	-5.0%	4.5%
6/1/23	Category 10	-6.0%	-6.0%
7/1/23	Category 10	-3.0%	-3.0%
8/1/23	Category 10	-0.1%	-0.1%
9/1/23	Category 10	-1.6%	-6.3%
10/1/23	Category 10	-7.2%	-69.1%

Re: Help coloring Deneb heatmap

giammariam — Wed, 18 Oct 2023 20:26:54 GMT

Thanks for the dataset, @Doug7983. Let me know if something like this scratches the itch.
Here is a gist that contains the spec. I've also included it below.

{ "resolve": {"scale": {"color": "independent"}}, "facet": { "row": { "field": "Category", "header": {"labelAngle": 0, "labelAlign": "left"}, "title": null } }, "spacing": -5, "spec": { "mark": {"type": "rect", "stroke": "white", "tooltip": true}, "encoding": { "x": { "field": "Date", "type": "ordinal", "title": null, "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)", "legend": { "direction": "horizontal", "title": null, "gradientLength": 120, "format": ".1%", "labelOffset": {"expr": "-child_height*0.6"}, "labelColor": { "expr": "luminance(scale('child_color', max(0, parseFloat(datum.label)/100))) < 0.5 ? '#fff' : '#000'" } } } } }, "data": { "name": "dataset" } } }

Re: Help coloring Deneb heatmap

Doug7983 — Thu, 19 Oct 2023 15:31:53 GMT

@giammariam

This is helpful, thank you. For context, I'm a long-time Tableau user and relatively new to both Power BI and Deneb. I have found it challenging to accurately describe what I'm trying to accomplish, so please bear with me.

Example (and screenshot below):

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. I’ve added labels here to see if maybe that helps:

Aside from all that, a question: I see in your spec that you are declaring the range of values. My data changes every day, so that wouldn't be practical. Is there a way to avoid that and have the range be dynamic?

Thanks,

Doug

Re: Help coloring Deneb heatmap

giammariam — Thu, 19 Oct 2023 16:05:01 GMT

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.

Re: Help coloring Deneb heatmap

Doug7983 — Thu, 19 Oct 2023 16:27:17 GMT

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

Re: Help coloring Deneb heatmap

giammariam — Thu, 19 Oct 2023 16:41:18 GMT

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.

Re: Help coloring Deneb heatmap

giammariam — Fri, 20 Oct 2023 13:02:41 GMT

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" } }

Re: Help coloring Deneb heatmap

Doug7983 — Fri, 20 Oct 2023 12:48:19 GMT

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.

Re: Help coloring Deneb heatmap

giammariam — Fri, 20 Oct 2023 13:04:25 GMT

@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 }

Re: Help coloring Deneb heatmap

Doug7983 — Fri, 20 Oct 2023 13:08:21 GMT

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

Doug

Re: Help coloring Deneb heatmap

Doug7983 — Fri, 20 Oct 2023 13:12:00 GMT

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

Re: Help coloring Deneb heatmap

giammariam — Fri, 20 Oct 2023 13:53:17 GMT

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"}],

Re: Help coloring Deneb heatmap

Doug7983 — Fri, 20 Oct 2023 17:16:06 GMT

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.

Re: Help coloring Deneb heatmap

giammariam — Fri, 20 Oct 2023 19:59:40 GMT

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

Re: Help coloring Deneb heatmap

Doug7983 — Fri, 20 Oct 2023 20:22:36 GMT

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

Re: Help coloring Deneb heatmap

giammariam — Fri, 20 Oct 2023 20:25:11 GMT

@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

Re: Help coloring Deneb heatmap

Doug7983 — Fri, 20 Oct 2023 20:28:38 GMT

@giammariam It says Jan 01, 2023

Re: Help coloring Deneb heatmap

Doug7983 — Fri, 20 Oct 2023 20:29:36 GMT

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