Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DW868990
Helper IV
Helper IV

Deneb - Vega0lite text mark on table heatmap help

Hi,

 

I have the below table heatmap and I am trying to layer a text mark on top of the x axis to show the total. The visual currently looks like this:

 

DW868990_0-1678112738483.png

 

As you can see the text mark is currently appearing for each level of the y axis but i only need it to appear the once at the top of each category on the x axis. My text mark code is below:

{
"mark": {
"type": "text",
"yOffset": -50,
"tooltip": true,
"fontSize": {"expr": "Size"}
},
"encoding": {
"text": {
"field": "group_band",
"aggregate": "max",
"type":"quantitative",
"format": ",.0f"
}
}
}

Any help appreciated. 

 

1 ACCEPTED SOLUTION

Thanks @DW868990. Is this what you're after?

* Note - there was no total provided in the dummy dataset for category 35-44. The value should show in the real dataset.

 

giammariam_0-1678132573088.png

Here's the gist. You'll just want to remove the url property from the data property when bringing into Deneb.

{
  "transform": [
    {
      "joinaggregate": [
        {"op": "sum", "field": "Count of Records", "as": "group_category"}
      ],
      "groupby": ["Category"]
    },
    {"filter": "datum['group_category'] != 0.0"},
    {
      "joinaggregate": [
        {"op": "sum", "field": "Count of Records", "as": "group_band"}
      ],
      "groupby": ["Band Size"]
    },
    {"joinaggregate": [{"op": "distinct", "field": "Band Size", "as": "band_count"}]},
    {
      "window": [{"op": "distinct", "field": "Band Size", "as": "band_count"},
      {"op": "dense_rank", "field": "Band Size", "as": "x_sort"}], "frame": [null,null], "sort": [{"field": "Band Size", "order": "ascending"}]
    },
    {"calculate": "datum['Band Size'] === 'Total' ? datum['band_count'] : datum['band_count']-1", "as": "x_sort"}
  ],
  "params": [
    {
      "name": "Size",
      "value": 9,
      "bind": {"input": "range", "min": 0, "max": 16, "step": 1, "name": " "}
    }
  ],
  "data": {
    "name": "dataset",
    "url": "https://raw.githubusercontent.com/Giammaria/PublicFiles/master/pbi/pbi_community_help/matrix_with_xy_totals/data/data.tsv"
  },
  "width": {"step": 50},
  "height": 360,
  "encoding": {
    "y": {"field": "Category", "title": null, "sort": "descending"},
    "x": {
      "field": "Band Size",
      "type": "ordinal",
      "sort": {"field": "x_sort"},
      "axis": {"orient": "bottom", "title": null}
    },
    "tooltip": [
      {"field": "Category"},
      {"field": "Band Size"},
      {"field": "Count of Records"}
    ]
  },
  "layer": [
    {
      "transform": [
        {"window": [{"op": "dense_rank", "as": "band_dr"}], "groupby": ["Band Size"]},
        {"filter": "datum['band_dr']===1"}
      ],
      "mark": {"type": "text"},
      "encoding": {
        "text": {
          "field": "group_band",
          "type": "notional"
        },
        "y": {
          "value": -15,
          "scale": null
        }
      }
    },
    {
      "transform": [
        {"filter": "datum['x_sort']===datum['band_count']"}
      ],
      "mark": {"type": "text"},
      "encoding": {
        "text": {
          "field": "group_category",
          "type": "notional"
        },
        "xOffset": {
          "expr": "50"
        }
      }
    },
    {
      "layer": [
        {
          "mark": {"type": "rect"},
          "encoding": {
            "color": {
              "condition": {
                "test": "datum['Band Size'] == 'Total'",
                "value": "white"
              },
              "field": "Count of Records",
              "scale": {"scheme": "lightgreyteal"},
              "legend": null,
              "tooltip": true
            }
          }
        },
        {
          "mark": {
            "type": "text",
            "tooltip": true,
            "fontSize": {"expr": "Size"}
          },
          "encoding": {
            "text": {
              "field": "Count of Records",
              "type": "quantitative",
              "format": ",.0f"
            },
            "color": {
              "condition": {"test": "datum.Metric === 0.0", "value": "#E3E9E9"},
              "scale": {"scheme": "lightgreyteal"},
              "tooltip": false
            }
          }
        }
      ]
    }
  ]
}


Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

View solution in original post

6 REPLIES 6
giammariam
Super User
Super User

Hey @DW868990, one way to achieve this is to use concatination (hconcat and vconcat). Check out the solution to this question here which has an example .pbix that you could use as a reference. Check out this video that really breaks down the idea of using concatenation layout composition in a way that is similar to what you're trying to achieve.

 

This could also be done without concatenation, and just layers. In this implementation, the main matrix, x totals, and the y totals would each have their own layers with their own x/y encodings and their own aggregation transforms.  

 

If this gets you going, please consider liking this reply and choosing it as the solution so others can find it. If you need additional help, please provide your spec and some sanitized data (here's how to provide data)



Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

Thank you, can't seem to get the vconcat working correctly. it seem to concatenate more than just the text layer. my current code is below. its only the text mark with the field group_band that needs concatenating, the rest needs to remain the same.

{
"transform":[
{
"joinaggregate":[{
"op": "sum",
"field": "Count of Records",
"as": "group_category"
}],
"groupby": ["Category"]
},
{
"filter": "datum['group_category'] != 0.0"
},
{
"joinaggregate":[{
"op": "sum",
"field": "Count of Records",
"as": "group_band"
}],
"groupby": ["Band Size"]
}],
"params": [
{
"name": "Size",
"value": 9,
"bind": {"input": "range", "min": 0, "max": 16, "step": 1, "name": " "}
}
],
"data": {"name": "dataset"},
"height": 360,
"encoding": {
"y": {
"field": "Category",
"title": null,
"sort": "descending"
},
"x": {
"field": "Band Size",
"type": "ordinal",
"sort": {"field": "Index"},
"axis": {
"orient": "bottom",
"title": null
}
},
"tooltip": [
{"field": "Category"},
{"field": "Band Size"},
{"field": "Count of Records"}
]
},
"layer": [
{
"mark": {"type": "rect"},
"encoding": {
"color": {
"condition": {"test": "datum['Band Size'] == 'Total'",
"value":"white"},
"field": "Count of Records",
"scale": {
"scheme": "lightgreyteal"
},
"legend": null,
"tooltip": true
}
}
},
{
"mark": {
"type": "text",
"tooltip": true,
"fontSize": {"expr": "Size"}
},
"encoding": {
"text": {
"field": "Count of Records",
"type":"quantitative",
"format": ",.0f"
},
"color": {
"condition": {
"test": "datum.Metric === 0.0",
"value": "#E3E9E9"
},
"scale": {"scheme": "lightgreyteal"},
"tooltip": false
}
}
},
{
"mark": {
"type": "text",
"yOffset": -50,
"tooltip": true,
"fontSize": {"expr": "Size"}
},
"encoding": {
"text": {
"field": "group_band",
"aggregate": "max",
"type":"quantitative",
"format": ",.0f"
}
}
}
]
}

Thanks for sharing your spec @DW868990. Can you also provide some sanitized data (here's how to provide data) with the same structure as your dataset? It'll greatly help with updating the spec to incorporate the changes you're after.



Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

No problem @giammariam Thank you very much for your help, please find link below.

Deneb Table Heatmap Sample Data 

Thanks @DW868990. Is this what you're after?

* Note - there was no total provided in the dummy dataset for category 35-44. The value should show in the real dataset.

 

giammariam_0-1678132573088.png

Here's the gist. You'll just want to remove the url property from the data property when bringing into Deneb.

{
  "transform": [
    {
      "joinaggregate": [
        {"op": "sum", "field": "Count of Records", "as": "group_category"}
      ],
      "groupby": ["Category"]
    },
    {"filter": "datum['group_category'] != 0.0"},
    {
      "joinaggregate": [
        {"op": "sum", "field": "Count of Records", "as": "group_band"}
      ],
      "groupby": ["Band Size"]
    },
    {"joinaggregate": [{"op": "distinct", "field": "Band Size", "as": "band_count"}]},
    {
      "window": [{"op": "distinct", "field": "Band Size", "as": "band_count"},
      {"op": "dense_rank", "field": "Band Size", "as": "x_sort"}], "frame": [null,null], "sort": [{"field": "Band Size", "order": "ascending"}]
    },
    {"calculate": "datum['Band Size'] === 'Total' ? datum['band_count'] : datum['band_count']-1", "as": "x_sort"}
  ],
  "params": [
    {
      "name": "Size",
      "value": 9,
      "bind": {"input": "range", "min": 0, "max": 16, "step": 1, "name": " "}
    }
  ],
  "data": {
    "name": "dataset",
    "url": "https://raw.githubusercontent.com/Giammaria/PublicFiles/master/pbi/pbi_community_help/matrix_with_xy_totals/data/data.tsv"
  },
  "width": {"step": 50},
  "height": 360,
  "encoding": {
    "y": {"field": "Category", "title": null, "sort": "descending"},
    "x": {
      "field": "Band Size",
      "type": "ordinal",
      "sort": {"field": "x_sort"},
      "axis": {"orient": "bottom", "title": null}
    },
    "tooltip": [
      {"field": "Category"},
      {"field": "Band Size"},
      {"field": "Count of Records"}
    ]
  },
  "layer": [
    {
      "transform": [
        {"window": [{"op": "dense_rank", "as": "band_dr"}], "groupby": ["Band Size"]},
        {"filter": "datum['band_dr']===1"}
      ],
      "mark": {"type": "text"},
      "encoding": {
        "text": {
          "field": "group_band",
          "type": "notional"
        },
        "y": {
          "value": -15,
          "scale": null
        }
      }
    },
    {
      "transform": [
        {"filter": "datum['x_sort']===datum['band_count']"}
      ],
      "mark": {"type": "text"},
      "encoding": {
        "text": {
          "field": "group_category",
          "type": "notional"
        },
        "xOffset": {
          "expr": "50"
        }
      }
    },
    {
      "layer": [
        {
          "mark": {"type": "rect"},
          "encoding": {
            "color": {
              "condition": {
                "test": "datum['Band Size'] == 'Total'",
                "value": "white"
              },
              "field": "Count of Records",
              "scale": {"scheme": "lightgreyteal"},
              "legend": null,
              "tooltip": true
            }
          }
        },
        {
          "mark": {
            "type": "text",
            "tooltip": true,
            "fontSize": {"expr": "Size"}
          },
          "encoding": {
            "text": {
              "field": "Count of Records",
              "type": "quantitative",
              "format": ",.0f"
            },
            "color": {
              "condition": {"test": "datum.Metric === 0.0", "value": "#E3E9E9"},
              "scale": {"scheme": "lightgreyteal"},
              "tooltip": false
            }
          }
        }
      ]
    }
  ]
}


Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

Fantastic, thank you so much!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.