登录查看更多内容

Power BI KPI using HTML and CSS

Matthew Spuffard

Expert in Data, Analytics & Project Controls | BaseOne.uk | Helping businesses make better decisions!

发布日期: 2023年1月25日

HTML (Hypertext Markup Language) and CSS (Cascading Style Sheets) are powerful tools that can be used to enhance the visual appeal and functionality of Power BI reports. By utilizing these languages, you can create custom KPIs (Key Performance Indicators) and visuals that are tailored to the specific needs of your business or organization.

One of the key benefits of using HTML and CSS in Power BI is the ability to create more visually appealing and informative graphics. For example, you can use HTML and CSS to create custom charts and graphs that are designed to effectively communicate the data you are trying to present. Additionally, you can use these languages to create custom formatting options, such as color schemes, font styles, and background images, which can help to make your reports more engaging and easier to understand.

In this tutorial, you'll learn how to create this simple conditional KPI.

First off, here's my toolkit.

jsfiddle.net - This is a code playground, where I can easily write code and see the result. I'll create the entire KPI in here first.
Microsoft Visual Studio Code - by far the best code editor, I'll use this to format and tweak the code.
Power BI - the final step will be to take the formatted code and create some variables in Power Bi to populate the KPI with data.

1. A KPI you can make in ten Minutes with HTML & CSS

1.1 - Basic HTML structure

Start by creating the basic structure of the HTML page by defining the head and body tags. In the head section, we will include the Google font link, which will be used for styling the KPI. The font I've chosen is Open Sans.

<html
<head>
  <link rel="preconnect" >
  <link rel="preconnect"  crossorigin>
  <link  rel="stylesheet">
</head>
<body>

</body>
</html>>

1.2 - HTML KPI Elements

Next, we will create the container for the KPI. We will use a div element with a class of kpiBox. Inside the div, we will include the KPI name, value, and difference.

<div class="kpiBox"
    <span class="kpiName" >HTML KPI</span>
    <span class="kpi"> 35%</span>
    <span class="kpiDifference">+3%</span>
</div>>

The kpiBox code should go in between the body tags of the HTML.

So, your HTML now looks like this.

<html
<head>
? <link rel="preconnect" >
? <link rel="preconnect"  crossorigin>
? <link  rel="stylesheet">
</head>
<body>

<div class="kpiBox">
? ? <span class="kpiName" >HTML KPI</span>
? ? <span class="kpi"> 35%</span>
? ? <span class="kpiDifference">+3%</span>
</div>

</body>
</html>>

OK, thats all the HTML we need.

Let's style it with CSS

1.3 - Styling the KPI box with CSS

Now, we will add the CSS styles for the KPI container. We will set the display to inline-block, width, and height to 10rem and 5rem, and background-color to #0E1218. We will also add a border-left of 10px and solid black color. We will also add padding-top and padding-left to add spacing between the elements inside the container.

.kpiBox {
? display: inline-block;
? width: 10rem;
? height: 5rem;
? background-color: #0E1218;
? border-left: 10px solid black;
? padding-top: 1rem;
? padding-left: 1.5rem;
? font-family: 'open sans';
}

1.4 - Style other KPI elements with CSS

Next, we'll style the KPI name, value, and difference. For the KPI name, we will set the display to block, font-size to 1.2rem and font-weight to 800. For the KPI value, we will set the font-size to 2rem. For the KPI difference, we will set the padding-left to 1rem, font-size to 1.3rem and color to grey.

.kpiName {
  display: block;
  font-size: 20px;
  font-weight: 800;
}

.kpi {
  font-size: 2rem;
}

.kpiDifference{
  padding-left: 1rem;
  font-size:1.3rem;
  color: grey;
}

1.5 - Conditional Formatting with CSS animation

Lastly, we will add an animation to change the color of the border and create the conditional formatting. What this does, is create gradient transition animation from one colour to another, between the values of 0 and 100%.

@keyframes color-gradient 
? ?0% {
? ? border-color: #d12421;
? }
? 50% {
? ? border-color: #ffb200;
? }
? 100% {
? ? border-color: #008979;
? }
}

1.6 - Adding the animation controls to the .kpibox

Then we will add some more CSS to the .kpibox to set the animation-fill-mode to forwards and animation-play-state to paused.

? animation: color-gradient 100s linear 1;
? animation-fill-mode: forwards;
? animation-play-state: paused;;

We don't want the animation to play, just to select a frame of the animation, and therefore a color within the gradient transition.

Your CSS in full looks like this.

.kpiBox
? display: inline-block;
? width: 10rem;
? height: 5rem;
? background-color: #0E1218;
? border-left: 10px solid black;
? padding-top: 1rem;
? padding-left: 1.5rem;
? font-family: 'open sans';
??
? animation: color-gradient 100s linear 1;
? animation-fill-mode: forwards;
? animation-play-state: paused;
}
.kpiName {
? display:block;
? font-size: 20px;
? font-weight: 800;
? color: white;
}
.kpi {
? font-size: 2rem;
? color: white;
}
.kpiDifference{
? padding-left: 1rem;
? font-size:1.3rem;
? color: grey;
}


@keyframes color-gradient {
? ?0% {
? ? border-color: #d12421;
? }
? 50% {
? ? border-color: #ffb200;
? }
? 100% {
? ? border-color: #008979;
? }

Here it all is in jsfiddle.

1.7 - Controlling the colour conditions

Next add the animation-delay control to your KPI HTML by editing the following line of code to look like this...

<div class="kpiBox" style="animation-delay: -100s;">

This uses the delay time in negative seconds to control the colour of the paused gradient animation. Cool huh?

2. Bringing the HTML & CSS together.

2.1 - Unless your visual allows seperate HTML and CSS measures (like the HTML Text Styler by BI Samauri) you'll need to embed the CSS into the HTML using the style tag.

To do this create a <style></style> tag within your body tags and paste the CSS between them.

So, it looks like this...

<!DOCTYPE html
<html>
<head>
? <link rel="preconnect" >
? <link rel="preconnect"  crossorigin>
? <link  rel="stylesheet">
</head>
<body>

<style>
.kpiBox{
? display: inline-block;
? width: 10rem;
? height: 5rem;
? background-color: #0E1218;
? border-left: 10px solid black;
? padding-top: 1rem;
? padding-left: 1.5rem;
? font-family: 'open sans';
??
? animation: color-gradient 100s linear 1;
? animation-fill-mode: forwards;
? animation-play-state: paused;
}
.kpiName {
? display:block;
? font-size: 20px;
? font-weight: 800;
? color: white;
}
.kpi {
? font-size: 2rem;
? color: white;
}
.kpiDifference{
? padding-left: 1rem;
? font-size:1.3rem;
? color: grey;
}


@keyframes color-gradient {
? ?0% {
? ? border-color: #d12421;
? }
? 50% {
? ? border-color: #ffb200;
? }
? 100% {
? ? border-color: #008979;
? }
}
</style>

<div class="kpiBox" style="animation-delay: -100s;">
? ? <span class="kpiName" >HTML KPI</span>
? ? <span class="kpi"> 35%</span>
? ? <span class="kpiDifference">+3%</span>
</div>

</body>
</html>>

Note how the CSS is in between the Style tags, and within the body of the HTML.

3 - Getting the code ready for Power BI

Stelvin Saji 1 年前

Image Transition Using HTML, CSS & JS

Stelvin Saji 2 年前

3.1 - Doubling the quotation marks

In Power BI, when writing DAX, we already use single quotation marks ". We also use them in HTML, and this causes conflicts.

So, to get around this, we need to double quote them.

I use Visual Studio code to do this, but you could use word, or any other text editor.

I'll simply paste in the code, and find/replace single marks, with double marks.

While I'm there I'll right click and format the code as well.

Now the HTML parts of your code should have double quotation marks instead of single.

After formatting, the whole script looks like this in VSCode.

Note how the Visual Studio Code has highlighted the double quotes as errors! Don't worry, this is fine. They're not errors in our Power BI measures.

Step 3 - Creating the visual in Power BI

3.1 - Setting up Power BI

I've created a simple dataset for this visual, using the CSS classes as the column headers to keep things simple. But you can create measures to include any data you like. Make sure you've downloaded your HTML visual form app.source as well.

3.2 - Creating the HTML Measure

Create a new measure, I've called mine HTML. Then create some variables using the VAR statement to define the values we will insert to the KPI.

HTML =

VAR KPI_Name = SELECTEDVALUE('HTMLTable'[KPI Name])
VAR KPI_Value = FORMAT(SELECTEDVALUE('HTMLTable'[KPI]),"0.0%")
VAR KPI_Difference = FORMAT(SELECTEDVALUE(HTMLTable[KPI Difference]),"0%")
VAR KPI_Cond = SELECTEDVALUE('HTMLTable'[KPI])*100 *-1 

RETURN
"


"

This is what the measures are doing.

KPI_Name - returns the KPI Title
KPI_Value - returns the main value as a percentage
KPI_Difference - returns the smaller value
KPI_Cond - returns the main KPI value as a whole negative number, so that it can be used in the animation to dictate what frame should be used to colour the left border. To do this, simply take the KPI percentage, multiply it by 100, and then multiply the result by negative 1. Easy.

3.3 - Adding the HTML/CSS

Between the quotes that are open, I will paste the entire HTML and CSS script.

HTML =
VAR KPI_Name = SELECTEDVALUE('HTMLTable'[KPI Name])
VAR KPI_Value = FORMAT(SELECTEDVALUE('HTMLTable'[KPI]),"0.0%")
VAR KPI_Difference = FORMAT(SELECTEDVALUE(HTMLTable[KPI Difference]),"0%")
VAR KPI_Cond = SELECTEDVALUE('HTMLTable'[KPI])*100 *-1 

RETURN
"
<!DOCTYPE html>
<html>
<head>
? <link rel=""preconnect"" href=""https://fonts.googleapis.com"">
? <link rel=""preconnect"" href=""https://fonts.gstatic.com"" crossorigin>
? <link href=""https://fonts.googleapis.com/css2?family=Open+Sans&display=swap"" rel=""stylesheet"">
</head>
<body>


<style>
.kpiBox{
? display: inline-block;
? width: 10rem;
? height: 5rem;
? background-color: #0E1218;
? border-left: 10px solid white;
? padding-top: 1rem;
? padding-left: 1.5rem;
? font-family: 'open sans';
? 
? animation: color-gradient 100s linear 1;
? animation-fill-mode: forwards;
? animation-play-state: paused;
}
.kpiName {
? display:block;
? font-size: 20px;
? font-weight: 800;
? color: white;
}
.kpi {
? font-size: 2rem;
? color: white;
}
.kpiDifference{
? padding-left: 1rem;
? font-size:1.3rem;
? color: grey;
}


@keyframes color-gradient {
? ?0% {
? ? border-color: red;
? }
? 50% {
? ? border-color: yellow;
? }
? 100% {
? ? border-color: green;
? }
}
</style>



<div class=""kpiBox"" style=""animation-delay: -50;"">
? ? <span class=""kpiName"" >HTML KPI</span>
? ? <span class=""kpi""> 35%</span>
? ? <span class=""kpiDifference"">+3%</span>
</div>


</body>
</html>
"

Then add the new measure to the visual.

3.4 - Plugging in the variables.

To add in the variable to the HTML, we need to put them between quotations and ampersands - "& &".

Remember we have four variables to add

KPI Title
KPI Value
KPI Difference
KPI Conditional

But if you notice the kpibox - animation-delay is a negative whole number. So we'll need to convert our percentage to the same.

Now the HTML for the KPI box looks like this.

<div class=""kpiBox"" style=""animation-delay: "& KPI_Cond&"s;"">
? ? <span class=""kpiName"" >"&KPI_Name&"</span>
? ? <span class=""kpi"">"&KPI_Value&"</span>
? ? <span class=""kpiDifference"">"&KPI_Difference&"</span>
</div>

4. Final code, and testing

Here is the final code for the measure in Power BI.

HTML =
VAR KPI_Name = SELECTEDVALUE('HTMLTable'[KPI Name])
VAR KPI_Value = FORMAT(SELECTEDVALUE('HTMLTable'[KPI]),"0.0%")
VAR KPI_Difference = FORMAT(SELECTEDVALUE(HTMLTable[KPI Difference]),"0%")
VAR KPI_Cond = SELECTEDVALUE('HTMLTable'[KPI])*100 *-1 

RETURN
"
<!DOCTYPE html>
<html>
<head>
? <link rel=""preconnect"" href=""https://fonts.googleapis.com"">
? <link rel=""preconnect"" href=""https://fonts.gstatic.com"" crossorigin>
? <link href=""https://fonts.googleapis.com/css2?family=Open+Sans&display=swap"" rel=""stylesheet"">
</head>
<body>


<style>
.kpiBox{
? display: inline-block;
? width: 10rem;
? height: 5rem;
? background-color: #0E1218;
? border-left: 10px solid white;
? padding-top: 1rem;
? padding-left: 1.5rem;
? font-family: 'open sans';
? 
? animation: color-gradient 100s linear 1;
? animation-fill-mode: forwards;
? animation-play-state: paused;
}
.kpiName {
? display:block;
? font-size: 20px;
? font-weight: 800;
? color: white;
}
.kpi {
? font-size: 2rem;
? color: white;
}
.kpiDifference{
? padding-left: 1rem;
? font-size:1.3rem;
? color: grey;
}

@keyframes color-gradient {
? ?0% {
? ? border-color: red;
? }
? 50% {
? ? border-color: yellow;
? }
? 100% {
? ? border-color: green;
? }
}
</style>

<div class=""kpiBox"" style=""animation-delay: "&KPI_Cond&"s;"">
? ? <span class=""kpiName"" >"&KPI_Name&"</span>
? ? <span class=""kpi"">"&KPI_Value&"</span>
? ? <span class=""kpiDifference"">"&KPI_Difference&"</span>
</div>

</body>
</html>
"

To test this, I swapped the percentage to a parameter. Here's the result.

4.1 - More KPIs?

If you want more KPIs in the same row, you can copy and paste the kpiBox HTML, making sure you keep it inside the div tags. Make sure you set up more VARs, and plug them into additional KPIs.

The CSS class we mentioned in the first sections (display: inline-block;) forces the KPI to stack horizontally, if you want it vertically, then remove this. Then use padding for more white space between them

4.2 - More colours?

If you want more colours, simply change the CSS keyframes to suit. You can have as many as you want.

ADD MORE VALUES HERE FOR ADDIONAL CONDITIONS

@keyframes color-gradient {
? ?0% {
? ? border-color: red;
? }
? 50% {
? ? border-color: yellow;
? }
? 100% {
? ? border-color: green;
? }
}

And thats it.

This tutorial will be available on the BaseOne website in the next few days, complete with downloads.

Follow me :)

If you want more content like this, follow me, and this newsletter.

Matthew Spuffard

The BaseOne.01

3,812 位关注者

ILFAT GALIEV

4 个月

I would like to suggest my HTML/SVG/Handlebars Visual, it brings the same functionality, but you don't need to store HTML/SVG template in DAX queries.

Brahim EL KASSRI

Data Analyst || Motion Graphic Designer

1 年

Thank you Matthew Spuffard ??

Martyn Booth

Senior Power BI Engineer @ FSP | Designing World Class Power BI Reports for Clients | Blogger at Not Just a Pretty Dashboard

1 年

This is brilliant! Well explained, keep up the great work Matthew ??

Kristjan Njakas

Helping companies make customers happy ?? | Google and Microsoft Certified Data Analyst ??| Software Developer ?? | Lifelong Learner ??

1 年

the correct website is jsfiddle.net, right? not .com

1 次回应

Railander Guilherme

Analista | Data Analytics | Ti | Desenvolvimento

1 年

Thank you for sharing . The information provided was valuable and well-explained. ????????

查看更多评论

要查看或添加评论，请登录

查看全部

Power BI KPI using HTML and CSS

Matthew Spuffard

Expert in Data, Analytics & Project Controls | BaseOne.uk | Helping businesses make better decisions!

First off, here's my toolkit.

1. A KPI you can make in ten Minutes with HTML & CSS

Let's style it with CSS

2. Bringing the HTML & CSS together.

3 - Getting the code ready for Power BI

领英推荐

Step 3 - Creating the visual in Power BI

4. Final code, and testing

4.1 - More KPIs?

4.2 - More colours?

Follow me :)

The BaseOne.01

3,812 位关注者

更多精彩文章

社区洞察

其他会员也浏览了

Login / Signup Form Using HTML, CSS & JS

Using CSS to Create Gartner Magic Quadrant Style Custom Graph

How To: Build A Simple App In HTML 5

HTML

HTML5 TOP FEATURES

CSS

Crafting Your First WebPage: Getting Started with HTML Editors and Code

Let's talk about fundamentals of HTML:-

HTML for Beginners 02 —?Working with Text, Links, and Images

What Is HTML? Hypertext Markup Language Basics Explained

First off, here's my toolkit.

1. A KPI you can make in ten Minutes with HTML & CSS

Let's style it with CSS

2. Bringing the HTML & CSS together.

3 - Getting the code ready for Power BI

领英推荐

Step 3 - Creating the visual in Power BI

4. Final code, and testing

4.1 - More KPIs?

4.2 - More colours?

Follow me :)

The BaseOne.01

3,812 位关注者

Harnessing the Power of Data Integration: A Strategic Imperative for Modern Businesses

2023年11月29日

Mastering Data Storage Management

2023年11月20日

Medallion Architecture: The Key to Superior Data Quality in Lakehouse

2023年11月7日

4 Tips On How To Achieve Sustainable Growth through Business Transformation

2023年10月12日

Leveraging Future Business Success: Driving ROI with Power BI, Fabric & Azure

2023年9月18日

7 Forgotten Metrics Every Power BI Developer Should Know

2023年8月30日

Power BI: Development: 5 Common Mistakes and Tips

2023年8月8日

The Rising Star: Understanding the Role of Analytics Engineer

2023年7月25日

Harnessing Generative AI: A Leap Forward in Software Development Productivity

2023年7月12日

Garbage In, Garbage Out: Understanding the Science of Quality Data Management for Predictable Outcomes.

2023年6月28日

社区洞察

其他会员也浏览了

Login / Signup Form Using HTML, CSS & JS

Using CSS to Create Gartner Magic Quadrant Style Custom Graph

How To: Build A Simple App In HTML 5

HTML

HTML5 TOP FEATURES

CSS

Crafting Your First WebPage: Getting Started with HTML Editors and Code

Let's talk about fundamentals of HTML:-

HTML for Beginners 02 —?Working with Text, Links, and Images

What Is HTML? Hypertext Markup Language Basics Explained