In Power BI, one of the frequently used visuals is a key performance indicator, or KPI card. It allows to quickly hone in on key values for your dashboard.
To avoid overwhelming users with incredibly high values, numbers are also automatically rounded off to the nearest billion, million or thousand.
However; dashboards in Excel are more likely to use the values that are in cells instead of on cards like in Power BI.
To take your dashboards to the next level, we can create cards that look and behave just like the ones in Power BI!
Getting Started
In this example, we will be using the WideWorldImporters database. This data can be acquired from a Microsoft Github link.
https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers
Information will be brought into Excel using Power Query, then added to the spreadsheet using cube formulas.
In this example, we’ll be getting the Total Sales Including Tax; just like in the examples above.
Any version of Excel from 2013 onward should work for this exercise.
We will develop our KPI card using the following steps:
- Getting the data from the database
- Create a DAX measure to get total sales
- Adding the data to the sheet using CUBEVALUE and slicers
- Formatting the data on the spreadsheet
- Creating the card
- Formatting the card
Let’s get started on building our KPI card in Excel!
Getting Data from the Database
Data will be acquired from the WideWorldImporters database using the Get Data option on the data tab.
The database and server can be selected at this point from the next menu.
This will bring up the tables that are available for import into the Excel data model.
Next, click on Select multiple items.
Once this is selected, the sales table and related tables need to be selected from the database.
- Select the Fact.Sale table from the database.
- Click the Select Related Tables button. All other tables related to the Fact.Sale table will be checked.
- Click the Load button to load the tables into the Excel data model.
At this point, we will have all the data we need to build our card!
Create a DAX measure to get Total Sales
In order to get the values from the data cube using the CUBEVALUE formula in Excel, a simple measure needs to be created in the data cube. This measure will be created using DAX.
To create a new measure, click on Measures under the Power Pivot menu item.
The new measure that will be created will be named Total Including Tax, which will be added on the Fact Sale table. The Fact Sale table is selected by default.
This measure will calculate all selected values in the total including tax field, so it can use the SUM formula.
=sum('Fact Sale'[Total Including Tax])
When the measure is completed, click the OK button to create the measure.
For a longer example on creating a measure, see my post on Creating a Measure using the Data Model.
Adding Data to the Sheet using CUBEVALUE and Slicers
Once the measure has been created, we will add the data to the spreadsheet using the CUBEVALUE formula in Excel.
In order to properly test the measure, we will also add 2 slicers to the spreadsheet for sales date and state.
Adding Slicers
Slicers are found in the Insert menu in the Filters section.
The first slicer that we will add is the state filter; this can be selected using the Slicer button. Once selected, choose the Data Model tab.
From the Insert Slicers menu, you can select a field for the slicer. For this example, we will select the State Province field and click OK.
The slicer has now been created!
For the date slicer, click on timeline instead of slicer. Select the data model, and all available fields will appear. Unlike the slicer, only date fields can be selected.
The date column from the Dimension Date field should be used as the timeline slicer.
This will create a slicer for all possible dates in our database.
Getting the value from the database using CUBEVALUE
The CUBEVALUE function is made up of 3 elements:
- The data model that is being used.
- The measure that is being calculated from the database.
- Any slicers that are being used to filter the measure.
A more in-depth post on cube formulas can be found on this site:
Cube Formulas – The Best Excel Formulas You’re Not Using
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total IncludingTax]",Timeline_Date, Slicer_State_Province)In Excel, the data model will always be ThisWorkbookDataModel. There is no need to memorize this; it will appear as soon as you enter a quotation mark.
Setting up the measure can be achieved the same way; once quotes are entered, the measure can be selected using a dropdown. After selecting measures, enter a period to select all possible measures.
The only measure should be Total IncludingTax which was created earlier.
At this point, the formula will work; it will show the total including tax for all sales. However; this value will not be able to be changed by the slicers!
To add the slicers, they need to be added as additional filters to the CUBEVALUE formula. These filters will use the name of the object; fortunately, the name will be available as the word “Slicer” or “Timeline” is added.
Once the slicers are added into the CUBEVALUE formula, the value will change as the slicer is filtered!
Formatting Data on the Spreadsheet
In Power BI, the numbers in the card are rounded off automatically; to trillion, billion, million or thousand.
Since the values in our data source do not go into the billions, we will need to be able to show data rounded to the nearest million or thousand. There is no need to account for negative values.
This can be accomplished through text formatting. Right click on the cell containing the CUBEVALUE formula, and go into Format Cells.
When adding a custom format, select the Custom category to be able to create a custom format.
Creating a Custom Format for Millions and Thousands
The required custom format is:
[>999999]$0.0#,," M";[>999]$0.0#," K";$0There are 3 elements to this custom format, one for the millions, one for thousands, and one if the result is under one thousand. Each element is separated by a semicolon.
[>999999] – This tells Excel to only use this format if the cell is over 999,999.
$0 – This section adds a dollar sign at the beginning of the number, and will always create a trailing zero (0.).
.0# – This formatting will always create 1 digit after the decimal point, and a second decimal point if the value does not round to zero.
Having a forced zero will display a value such as $1,001,800 as $1.0M, instead of showing an awkward period with no number after.
,, ” M” – Adding 2 commas will display the number in the cell in millions. A ” M” is added to denote that the value is in millions.
Finally, add a semicolon to complete the millions formatting.
[>999]$0.0#,” K”; – For thousands, the only differences are that there is only one comma, and the number shows as “K” instead of “M”.
The formatting for under $1,000 is simply $0.
Once complete, your number should appear properly formatted in the sample box. Click OK to confirm the formatting.
The number will now appear properly on the spreadsheet. It’s time to get this number into a text box!
Creating the KPI Card
Before using the value on the spreadsheet in a KPI card, we need to give the cell containing the CUBEVALUE formula a name.
This can be done in the text box above A1; we will name the cell TotalSales.
Now, you can create a text box. This object is located under the Insert menu. The text box should be about 5 rows high, and 2-3 columns wide. However; you can make it as large or as small as you want.
While the text box is selected, enter a reference to the cell with the CUBEVALUE formula, =TotalSales.
Adding the field name
To add the field name, total including tax, a second text box is required. This is accomplished by adding a second text box with the same width under the first text box.
Unlike adding a number, text can be typed directly into a text box.
Once the second box is created, click on each box (holding down SHIFT) then right click.
By selecting Group, the two text boxes can be moved together as a single object.
Formatting the KPI Card
The final step is to make the card look more like a Power BI KPI card.
This involves:
- Adjusting fonts to the fonts that are in Power BI.
- Resizing and aligning text.
- Changing colors to Power BI colors.
- Clearing interior borders and adding exterior border.
Fonts
Fonts for a text box can be adjusted for an entire text box by clicking on the Home ribbon and selecting a font.
The default fonts that are used in Power BI for a card are:
Data Box (containing the number) – Bahnshrift
Text Label (containing the field name) – Segoe UI
Resizing and Aligning Text
Text should be aligned both horizontally and vertically within the text box. This can be done for the entire group, there is no need to do each box individually.
Next, resize the text in the data box to 2-4X the size of the text label.
In the example below, I have increased the data box to 24pt font while decreasing the label to 9pt font.
If the card is too big, this is a good opportunity to resize the card.
Changing to Power BI Colors
The data label can be left in black (#000000); but the data label is more of an off-grey (#605E5C).
After selecting the label text box, click on font color and select More Colors.
Once in More Colors, select Custom. To pick the same color as Power BI, enter the hex code (#605E5C), then click OK.
Clear Borders and Add External Border
At this time, it is not possible to add only external borders to the text box group. As a result, to add only external borders, three steps are required.
- Remove all borders from the text boxes.
- Add an unfilled rectangle box around the existing group.
- Add the new rectangle box to the text box group.
Remove all borders from the text boxes
To remove borders from the text box group, click on the group, then Shape Format.
Under Shape Format, click on Shape Outline and select No Outline.
At this point, the text box has no border.
Add an unfilled rectangle box around the existing group.
Under Insert > Shapes, select a rectangle.
Create a rectangle over the KPI card that has been created. Don’t worry about the color at this point!
Finally, click on the Shape Styles drop down to change the format of the new rectangle. The style should be changed to Transparent, Colored Outline – Black Dark 1.
The only step left at this point is to group the new rectangle with the existing text boxes!
Add the new rectangle box to the text box group
First, click on the existing text box group. It is important not to click on one of the single text boxes!
Then, hold down SHIFT and click on the rectangle border. Both elements should be highlighted.
Finally, right click and select Group.
The KPI card is now complete! Congratulations!
The KPI box that we have created on this post is available for download. Please note that the database is not included in this file.
Conclusion
The ability to add floating text boxes provides a great way to build dashboards in Excel! These provide a striking contrast to simply using data in ranges.
When combined with slicers and other visualizations, it is possible to build a layout in Excel similar to what can be found in Power BI.
There are still many features in Power BI that are not available in Excel worksheets, including:
- Data Sharing
- Data Security
- Cross filtering between visuals
When building a dashboard, always make sure to use the right tool for what users require!