Formatting is often overlooked when creating Power BI reports, yet it is something that really elevates a report. I highly recommend spending the time to format or set up a style of formatting reports when you start using Power BI.
To help you on your journey I am doing a Power BI Formatting blog series where we are going to look at different ways you can format Power BI reports.
In today’s example we are going to look formatting options specific to table and matrix visuals.
Here is our current matrix visual with default formatting:
Formatting options are available by selecting the paint roller icon on the visualization tab.
- Style
- This allows you to select between pre-defined formatting for your visual.
- Grid, Column headers, Row headers and Values
- If you do not want to use a preset style you can use these four areas to design your table style.
- In the below example I turned on the vertical grid and formatting the color and thickness of both the grid and outline. I formatted the column and row headers to be framed in by the outline, gave the column headers a center alignment and increased the stepped indentation for the rows. I removed the +/- icons from the row headers and changed the font color for the alternating rows.
- Subtotals
- Subtotals can be turned off if you do not want the different categories to sum the details. If you have subtotals turned on you can also set them per row level and determine which levels you want to total.
- If you have columns on a matrix visual, you will also have the option to turn on subtotals for the different columns.
- Grand total
- Turning this on will give you the total line that appears at the bottom of your table/matrix. We also accomplished this with the row level subtotals by having it turned on at the top row level.
- Field formatting
- If you want just one column to be formatted differently you can apply field formatting and change the font, background and alignment. This can be applied to the values, header and totals. If you want to set this up you do need to format each column individually.
- Conditional formatting
- Like field formatting conditional formatting is done by individual column and you can set up the column to format based on a color scale, field value or rules. The background and font colors can be changed, data bars can be added or icons can appear. The fields used for formatting do not need to appear on the visual.
- In the below example I set the color of the job name font to be defined by the field value of a custom column created that labels a job green or yellow depending on whether the original contract amount is over or under $100,000. Icons were added to the total revised forecast cost depending on a set of rules where measures were used to calculate the percent of revenue based on forecasted cost compared to the original contract amount. Finally, data bars were added to the total actual cost column to easily see which jobs have cost the most.
- For more information on how to set up conditional formatting see a previous set of blog posts regarding conditional formatting.
Although I don’t expect anyone to use all the formatting options available for each table or matrix visual I hope this has helped you see some of the options available to use. Don’t be afraid to play around with formatting. If at any time you do not like what you have done you can always select the “Revert to Default” option at the bottom of any area and the visual will revert back to default settings.
– Jerica Coleman, CRM and Power BI Consultant
