Paginated Report Tutorial: 100% Visualization

In this Paginated Reports tutorial we will create an eye-catching visual that has been termed the “100% visualization” using Power BI Report Builder. This visual is excellent for displaying any percentage-based metric. Before diving into the tutorial, a big thanks to Jason Thomas who first demonstrated how to build this type of visual in SSRS.

To get started, all you will need is Power BI Report Builder installed on your machine. If you don’t already have this installed, you can find the free download for Power BI Report Builder here.

Add a Dataset

For the purposes of this tutorial, I will be creating an example dataset showing the remaining inventory at a bicycle shop. You can just as easily follow along with your own dataset as long as you have several metrics already in percentage format.

  1. Open Power BI Report Builder and right click on Datasets in the Report Data pane to add a dataset.
    Add a dataset
  2. Within Dataset Properties select New to add a data source. Within Data Source Properties select Enter Data form the dropdown and click OK.
  3. Select Query Designer and create a Category and Percentage column. Enter the following data to create the example dataset.
    Table with two columns: category and percentage. The category column is populated with the following: road, mountain, hybrid, fixie. The percentage column is populated with the following: 88, 38, 27, 99.
  4. By default, all entered data is type String. Change the data type of the Percentage column to Integer by right clicking on the column heading and selecting Change Type and then Integer. Click OK
  5. You should now have a simple dataset with fields for Category and Percentage.
    Dataset successfully added.

Build the Visual

  1. Insert a Table and add rows and columns so you have a 10 by 10 grid. Adjust the size of the cells in the Table to be uniform squares (i.e. 0.25, 0.25) by selecting a row and adjusting the width.
    10 x 10 table
  2. Fill in the table with values from 1 to 100 as seen below.
    10 x 10 table with values from 0 to 100
  3. Now highlight all cells in the table to set the BorderColor to White, the BorderStyle to Solid, BorderWidth to 4pt, and the FontSize to 2pt.
  4. Right click outside of the report canvas to select Report Properties. Select the Code tab and enter the following custom code. The function GetColor() is used to select new colors for each category type and the function ColorDWB() is used to get a lighter shade of the selected color.
    Public Shared Function ColorDWB(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal Neutral As Decimal, ByVal ColStr As String) As String Dim ColVar1 As Integer Dim ColVar2 As Integer Dim ColVar3 As Integer ‘Split the #RGB color to R, G, and B components ColVar1=Convert.ToInt32(left(right(ColStr, 6),2),16) ColVar2=Convert.ToInt32(left(right(ColStr, 4),2),16) ColVar3=Convert.ToInt32(right(ColStr, 2),16) ‘Find Largest Range Dim decPosRange As Decimal = Math.Abs(MaxPositive – Neutral) ‘Find appropriate color shade Dim Shd As Decimal = 255     Dim iColor1 As Integer Dim iColor2 As Integer Dim iColor3 As Integer Dim strColor As String ‘Reduce a shade for each of the R,G,B components iColor1 = ColVar1 + CInt(Math.Round((MaxPositive-Value) * ((Shd – ColVar1) / decPosRange))) iColor2 = ColVar2 + CInt(Math.Round((MaxPositive-Value) * ((Shd – ColVar2) / decPosRange))) iColor3 = ColVar3 + CInt(Math.Round((MaxPositive-Value) * ((Shd – ColVar3) / decPosRange))) ‘Return the new color strColor = “#” & iColor1.ToString(“X2”)  & iColor2.ToString(“X2”) & iColor3.ToString(“X2”)    Return strColor End Function Private colorPalette As String() = { “#C85200”, “#FF800E”, “#5F9ED1”, “#2CA02C”, “#A59D93”, “#B8341B”, “#352F26”, “#F1E7D6”, “#E16C56”, “#CFBA9B”} Private count As Integer = 0 Private mapping As New System.Collections.Hashtable() Public Function GetColor(ByVal groupingValue As String) As String    If mapping.ContainsKey(groupingValue) Then       Return mapping(groupingValue)    End If    Dim c As String = colorPalette(count Mod colorPalette.Length)    count = count + 1    mapping.Add(groupingValue, c)    Return c End Function
  5. With all cells highlighted, add the follow expression for the BackgroundColor and Color fields.
    =iif(Me.Value <= Fields!Percentage.Value, Code.GetColor(Fields!Category.Value), Code.ColorDWB(2, 10, 1, Code.GetColor(Fields!Category.Value)))
  6. Insert a Rectangle and place your Table inside. Within the Rectangle, add a Textbox above and below the Table, formatting the BackgroundColor as desired.
  7. From your dataset, drag the Category field to the top Textbox and the Percentage Field to the bottom Textbox. Center the text horizontally and vertically and then set the Color to White and the FontSize to 20pt.
  8. Select the Textbox containing the Percentage field. In Number section of the Home ribbon, select % as the data type. Next, right click this Textbox and select Expression. Enter the following to put the data into proper percentage format.
    =Fields!Percentage.Value/100
  9. Now add a Matrix to the canvas, adding the Category field to the Column Group. This Matrix will allows us to create a 100% visualization for each of our Categories.
  10. Drag your Rectangle containing your Table and two Textboxes into the Data field of the Matrix. You can now delete the first row and column of the Matrix. When deleting the first row, make sure you delete the rows only, not the associated groups.
  11. Your Matrix should now match with the image shown below.
  12. You can now run your report to display an eye-catching 100% visualization.

Leave a Reply

Your email address will not be published. Required fields are marked *