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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
H_insight
Helper V
Helper V

Dened - Custom Matrix with double headers

Hi @dm-p


I hope you can help me here to deliver a matrix view in Deneb to look like this:

 

H_insight_0-1668506895172.png

 

I aim to show the Category and Sub-category at the top of the matrix and colour the cells based on the values.

So far, here is my code:

{
  
  "data": {"name": "dataset"},
  "encoding": {
    "y": {
      "field": "Product",
      "title": null
    },
    "x": {
      "field": "Sub Cat",
      "axis": {
        "orient": "top"
      }
    }
  },
  "layer": [
    {
      "mark": {"type": "rect"},
      "encoding": {
        "color": {
          "field": "Value",
          "scale": {
            "scheme": "lightgreyred"
          },
          "legend": null
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "tooltip": true,
        "fontsize": 14
      },
      "encoding": {
        "text": {
          "field": "Value",
          "type": "quantitative",
          "format": ",.0f"
        },
        "color": {"value": "black"}
      }
    } 
  ]
}

And below is the output I got so far:

H_insight_2-1668507163592.png

I was hoping that when defining the "x" "field", I could add multiple fields ... but I am unsure how to do it.

 

Many thanks

Sample file

1 ACCEPTED SOLUTION

Hey @H_insight. I believe I got everything implemented. Take a look and let me know. 

 

Notes: 

  • When adjusting the size of the visual, you'll want to adjust the step width/height and the xOffset/yOffset for the rects sitting behind the row and column headers. You'll also want to adjust font size accordingly.
  • Although there are alternatives, I added a calculated column to implement the logic for a custom sort order. The column is called "Sort Order". Here you should be able to implement any logic you'd like to determine the sorting of the products.  

 

Spec: 

{
  "data": {"name": "dataset"},
  "facet": {
    "column": {
      "field": "Category",
      "title": null,
      "header": {"labelFontSize": 14}
    }
  },
  "spec": {
    "width": {"step": 70},
    "height": {"step": 25},
    "encoding": {
      "y": {
        "field": "Product",
        "title": null,
        "sort": {"field": "Sort Order"},
        "axis": {
          "labelColor": "white",
          "labelFontWeight": 700
        }
      },
      "x": {
        "field": "Sub Cat",
        "axis": {
          "orient": "top",
          "labelAngle": 0,
          "labelColor": "white",
          "labelFontWeight": 700
        },
        "title": null
      }
    },
    "layer": [
      {
        "description": "x-axis background rects",
        "mark": {"type": "rect"},
        "encoding": {
          "y": {"value": 0},
          "yOffset": {"value": -25},
          "color": {
            "condition": {
              "test": "datum['Sub Cat'] == 'Overall'",
              "value": "#C45920"
            },
            "value": "#325964"
          }
        }
      },
      {
        "description": "y-axis background rects",
        "mark": {
          "type": "rect",
          "opacity": 0.5
        },
        "encoding": {
          "x": {"value": 0},
          "xOffset": {"value": -80},
          "color": {"value": "black"}
        }
      },
      {
        "description": "matrix value rects",
        "mark": {"type": "rect"},
        "encoding": {
          "color": {
            "field": "Value",
            "type": "nominal",
            "scale": {
              "domain": [1, 2, 3, 4, 5],
              "range": [
                "grey",
                "amber",
                "green",
                "red",
                "lightBlue"
              ]
            },
            "legend": null
          }
        }
      },
      {
        "mark": {
          "type": "text",
          "tooltip": true,
          "size": 14
        },
        "encoding": {
          "text": {
            "field": "Value",
            "type": "quantitative",
            "format": ",.0f"
          },
          "color": {
            "field": "Value",
            "type": "quantitative",
            "scale": {
              "domain": [1, 2, 3, 4, 5],
              "range": [
                "white",
                "white",
                "white",
                "white",
                "black"
              ]
            },
            "legend": null
          }
        }
      }
    ]
  },
  "resolve": {
    "scale": {
      "x": "independent",
      "y": "independent"
    }
  }
}


.pbix here



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

9 REPLIES 9
giammariam
Super User
Super User

Hi @H_insight. Looks like you just need to implement faceting. I'd be happy to take a look but I can't access your sample file.



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!

Hi @giammariam , thanks for giving me your time.

 

Here is an updated file link, or in here.

Alright, I made a few updates. The first one is the main part that gives you the matrices side-by-side. 

  1. Implemented column faceting on category
  2. Added a transform to correctly sort the y-axis alphabetically based on the product number
  3. Rotated the x-axis labels to have an angle of 0

* Important note - with layout composition (in this case, faceting), you have to specifiy the width and height to be used for the individual charts. This means that if you resize the visual in the Power BI formatting properties, you'll want to update the width and height in the spec as well.

 

Let me know if this is what you are after. Happy to help if you have additional questions (I'm looking for Deneb/Vega/Vega-Lite practice).

giammariam_1-1668560572819.png

Spec:

{
  "data": {"name": "dataset"},
  "transform": [
    {
      "calculate": "parseInt(replace(datum['Product'], 'Product ', ''))",
      "as": "sortOrder"
    }
  ],
  "facet": {
    "column": {
      "field": "Category",
      "title": null,
      "header": {"labelFontSize": 14}
    }
  },
  "spec": {
    "width": 525,
    "height": 320.5,
    "encoding": {
      "y": {"field": "Product", "title": null, "sort": {"field": "sortOrder"}},
      "x": {
        "field": "Sub Cat",
        "axis": {"orient": "top", "labelAngle": 0},
        "title": null
      }
    },
    "layer": [
      {
        "mark": {"type": "rect"},
        "encoding": {
          "color": {
            "field": "Value",
            "scale": {"scheme": "lightgreyred"},
            "legend": null
          }
        }
      },
      {
        "mark": {"type": "text", "tooltip": true, "size": 14},
        "encoding": {
          "text": {"field": "Value", "type": "quantitative", "format": ",.0f"},
          "color": {"value": "black"}
        }
      }
    ]
  }
}

 

pbix file here



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!

This is exciting for sure! Thank you.

I have done a quick test where I tried to add additional categories, but it messes up the whole layout, and I get gaps in the structure. (attached file). It may relate to the fact that each category has a different sub-cat.

H_insight_0-1668598128274.png

 

I do have additional questions if you are still happy to help:

  • Can I set a rule for the product field to change the background color where "Overall" will be "brown" and the rest is "darkblue", and the font is "white"? H_insight_1-1668599630106.png
  • Can I set a rule to set the background color for "category" to be "black" and the font is "white"? H_insight_2-1668599671321.png
  • Can I set a specific rule to sort by specific product? i.e. I want the product to start with Product 5, Product 2,...etc.
  • Can I set a rule for coloring the values? so instead of having a color scheme, I would have: if value = 1 then color = "grey", if value = 2, then color = "amber", 3 ="green", 4= "red"a and 5="lightblue". H_insight_3-1668599720718.png

 

Hey @H_insight. I believe I got everything implemented. Take a look and let me know. 

 

Notes: 

  • When adjusting the size of the visual, you'll want to adjust the step width/height and the xOffset/yOffset for the rects sitting behind the row and column headers. You'll also want to adjust font size accordingly.
  • Although there are alternatives, I added a calculated column to implement the logic for a custom sort order. The column is called "Sort Order". Here you should be able to implement any logic you'd like to determine the sorting of the products.  

 

Spec: 

{
  "data": {"name": "dataset"},
  "facet": {
    "column": {
      "field": "Category",
      "title": null,
      "header": {"labelFontSize": 14}
    }
  },
  "spec": {
    "width": {"step": 70},
    "height": {"step": 25},
    "encoding": {
      "y": {
        "field": "Product",
        "title": null,
        "sort": {"field": "Sort Order"},
        "axis": {
          "labelColor": "white",
          "labelFontWeight": 700
        }
      },
      "x": {
        "field": "Sub Cat",
        "axis": {
          "orient": "top",
          "labelAngle": 0,
          "labelColor": "white",
          "labelFontWeight": 700
        },
        "title": null
      }
    },
    "layer": [
      {
        "description": "x-axis background rects",
        "mark": {"type": "rect"},
        "encoding": {
          "y": {"value": 0},
          "yOffset": {"value": -25},
          "color": {
            "condition": {
              "test": "datum['Sub Cat'] == 'Overall'",
              "value": "#C45920"
            },
            "value": "#325964"
          }
        }
      },
      {
        "description": "y-axis background rects",
        "mark": {
          "type": "rect",
          "opacity": 0.5
        },
        "encoding": {
          "x": {"value": 0},
          "xOffset": {"value": -80},
          "color": {"value": "black"}
        }
      },
      {
        "description": "matrix value rects",
        "mark": {"type": "rect"},
        "encoding": {
          "color": {
            "field": "Value",
            "type": "nominal",
            "scale": {
              "domain": [1, 2, 3, 4, 5],
              "range": [
                "grey",
                "amber",
                "green",
                "red",
                "lightBlue"
              ]
            },
            "legend": null
          }
        }
      },
      {
        "mark": {
          "type": "text",
          "tooltip": true,
          "size": 14
        },
        "encoding": {
          "text": {
            "field": "Value",
            "type": "quantitative",
            "format": ",.0f"
          },
          "color": {
            "field": "Value",
            "type": "quantitative",
            "scale": {
              "domain": [1, 2, 3, 4, 5],
              "range": [
                "white",
                "white",
                "white",
                "white",
                "black"
              ]
            },
            "legend": null
          }
        }
      }
    ]
  },
  "resolve": {
    "scale": {
      "x": "independent",
      "y": "independent"
    }
  }
}


.pbix here



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 very much @giammariam. The potential of Deneb is crazy!! Thanks again.

I have a follow-up question when you are free, please:

 

Is there a way to remove the duplicate x-axis? So it will only show the x-axis at the beginning of the matrix? (this will save space)

H_insight_0-1668676204844.png

 

Also, how to do a text wrap? Some product names are too long, and they skew the table.

 

Hey @H_insight. I have updated this so there is only one y-axis. Moving forward, It's important to not only copy the spec, but the config as well since that has now been updated due to the visual's increased complexity.

giammariam_0-1668794354182.png

 

For the wrapping, it gets tricky with vega-lite. The only way I'm aware of doing this is by splitting each label into an array based on some delimiter (e.g. a space), or some character count. Then each element in the array for the label could appear on a new line. The other alternative is to use limit. Limit essentially truncates a text string after a specified number of characters and then adds "..." to the end of the text. You can play with limit here by using the input slider. Let me know your thoughts.

Latest .pbix here



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!

Hey @giammariam, I can't thank you enough for your help.

 

I will take note of the Config tab in the future. For some reason, I thought the app Specification would set it, but good to know that there is room for changes.

 

Deneb has so much potential, and the capability so far seems crazy!

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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