I wish to add a KPI card similar to the photo, which is from Kerry Kolosko’s page: https://kerrykolosko.com/conditional-kpi-cards-with-deneb/
Does anyone have the code or link to the pbix of such a card, which they wouldn’t mind sharing? Thank you for any assistance you can provide!!
Hi @lancemillercpa98. I cut-and-pasted Kerry’s code for one of the KPI’s from the comments section of the page you noted, but need a dataset to run and verify it presents the KPI you want in the fashion you want.
Alternatively, you could combine the code for the text fields from Kerry’s code and the bar and tick mark code from my bullet chart example below:
If you’d like to pursue further, please prepare and upload a sample dataset (say in Excel) and work-in-progress PBIX.
Greg
All of Kerry’s cards are available in the Deneb sample workbook, which you can download from the AppSource listing.
Thank you Greg. As always, much appreciated!
Wow. They sure are. Thank you dm-p!!
Hello Greg,
I too am after the similar type of visual that Lance posted. I don’t need to have multiple bars as aim is to be a summary of BUDGET vs SPEND. In the example .pbix you uploaded it has multiple rows per country - what would we need to update in the Vega lite code if we don’t need the individual country but just a total summary.
A second requirement is to color code the SPEND bar to either “green” or “red” depending if spend is over or under budget.
I’ve uploaded a similar dataset to give u idea of what I’m looking at creating.
Many Thanks,
B
Deneb Example - Simple bullet chart.pbix (5.7 MB)
Hi @b33.wishart.
Your request is unclear. Also, there is only 1 row of data in your sample PBIX and no Deneb code. If you’d like further assistance, please update your PBIX with more data, create a native Power BI table visual of the data you’d like to see, and create a Deneb visual that can then be marked-up with the changes you’d like to see.
Greg
P.S.: There are many examples that can be used as starting points on the Vega-Lite website https://vega.github.io/vega-lite/examples/
B, try the below for a horizontal visualization. It changes from black to red based on variance.
{
“title”:{
“orient”: “top”,
“offset”: -1,
“text”: “Units Sold”,
“font”: “Segoe UI”,
“fontSize”: 16,
“fontWeight”: “normal”,
“fontStyle”: “normal”
},
“data”: {“name”: “dataset”},
“transform”: [
{
“calculate”: “datum[‘Actual’] - datum[‘Prior Year’]”,
“as”: “PY Variance”
},
{
“calculate”: “datum[‘PY Variance’] / datum[‘Prior Year’]”,
“as”: “PY % Var”
},
{
“calculate”: “datum[‘Actual’] - datum[‘Budget’]”,
“as”: “Budget Variance”
},
{
“calculate”: “datum[‘Budget Variance’] / datum[‘Budget’]”,
“as”: “Budget % Var”
},
{
“calculate”: “datum[‘Actual’] - datum[‘Stretch1’]”,
“as”: “Stretch1 Variance”
}
],
“encoding”: {
“x”: {
“type”: “quantitative”,
“scale”: {“nice”: false},
“title”: null,
“axis”: null
}
},
“layer”: [
{
“description”: “PRIOR YEAR BAR”,
“mark”: {
“type”: “bar”,
“color”: {“expr”: “pbiColor(0,0.7)”},
“size”: 30,
“yOffset”: 7,
“tooltip”: true
},
“encoding”: {
“x”: {“field”: “Prior Year”},
“y”: {
“field”: “”,
“axis”: null
},
“tooltip”: [
{
“field”: “Actual”,
“type”: “quantitative”,
“formatType”: “pbiFormat”,
“format”: “#,#”
},
{
“field”: “Prior Year”,
“type”: “quantitative”,
“formatType”: “pbiFormat”,
“format”: “#,#”
},
{
“field”: “PY Variance”,
“title”: “+/-:”,
“type”: “quantitative”,
“formatType”: “pbiFormat”,
“format”: “#,#”
},
{
“field”: “PY % Var”,
“title”: “%:”,
“type”: “quantitative”,
“formatType”: “pbiFormat”,
“format”: “#%”
}]
}
},
{
"description": "ACTUAL YEAR BAR",
"mark": {
"type": "bar",
"size": 18,
"color": {"expr": "datum ['Actual'] < datum ['Prior Year'] ? 'red':'black' "},
"yOffset": 5,
"tooltip": true
},
"encoding": {
"x": {"field": "Actual"},
"y": {
"field": "",
"axis": null
},
"tooltip": [
{
"field": "Actual",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,#"
},
{
"field": "Prior Year",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,#"
},
{
"field": "PY Variance",
"title": "+/-:",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,#"
},
{
"field": "PY % Var",
"title": "%:",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#%"
}]
}
},
{
"description": "ACTUAL YEAR TEXT",
"mark": {
"type": "text",
"dx": 0,
"dy": 0,
"xOffset": -2,
"yOffset": 14,
"angle": 0,
"align": "right",
"baseline": "bottom",
"font": "sans-serif",
"fontSize": 16,
"fontStyle": "normal",
"fontWeight": "normal",
"color": "white",
"tooltip": "true",
"limit": 0
},
"encoding": {
"x": {
"aggregate": "max",
"field": "Actual",
"type": "quantitative"
},
"text": {
"field": "Actual",
"format": ".2s"
}
,
"tooltip": [
{
"field": "Actual",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,#"
},
{
"field": "Prior Year",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,#"
},
{
"field": "PY Variance",
"title": "+/-:",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,#"
},
{
"field": "PY % Var",
"title": "%:",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#%"
}]
}
},
{
"description": "VARIANCE TEXT",
"mark": {
"type": "text",
"color": {"expr": "datum ['Actual'] < datum ['Prior Year'] ? 'red':'black' "},
"dx": 0,
"dy": 0,
"xOffset": 0,
"yOffset": -18,
"angle": 0,
"align": "center",
"baseline": "top",
"font": "sans-serif",
"fontSize": 10,
"fontStyle": "normal",
"fontWeight": "normal",
"limit": 0
},
"encoding": {
"x": {
"aggregate": "max",
"type": "quantitative"
},
"text": {
"field": "PY % Var",
"title": "%:",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#% vs PY"
}
,
"tooltip": [
{
"field": "Actual",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,#"
},
{
"field": "Prior Year",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,#"
},
{
"field": "PY Variance",
"title": "+/-:",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,#"
},
{
"field": "PY % Var",
"title": "%:",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#%"
}]
}
}
]
}
Thank you so much Lance. I really appreciate the length of the code you provided as I am a newbie. I tried to put in my visual but couldn’t seem to get it to work. If its not too cheeky do you have a sample .pbix file with this applied and working which will help me debug mine at all ? No worries if you can’t. Cheers, Bee
Hello Greg - thank u for response. I only have 1 row as my example is needed to provide a simple bar of the TOTAL as there is not a requirement/need to distribute across any dimensions - simple budget vs spend thats it. I am a newbie so that is why no code yet in the .pbix file I supplied just the data I am trying to create the visual with. It looks like what Lance provided is close to what I need with a few more bells n whistles which is great. I just had some trouble getting it to work. All good I will go learn some more to figure out. Thanks again Bee