Theme colours for designing reports in Power BI

[EnterpriseDNA]

One thing, unfortunately missing currently from Power BI is being able to create theme colours for your reports. The only way to change the colours within a report is to go and change the HEX values manually within the formatting section. You can obviously use the current palette, but quite frequently this isn't enough. There may be corporate colours to use or you even just may want your report to pop a bit more with some vibrant and coordinated colours.

This is simple in excel as themes are automatically setup to pre-populate the colour palette when they are selected, making creating charts and worksheet formatting relatively easy, but in Power BI it's a different story at moment.

Also, excel does not actually give you HEX values anywhere in the palette itself.

It shows only RGB (or HSL) values. So historically the only way I was able to get a range of HEX values for colours was to find a converter online that did the job for me. This took a lot of time as you can imagine, especially if I wanted an entire palette of colours. (I actually did this once and vowed never again!)

Changing colours is quite a manual task in Power BI, that's why I have voted here and hope that you will also. Getting theme colour selection embedded into Power BI desktop will almost instantly improve the look and feel or every users reports, let only save a huge amount of time manually having to update HEX values.

So, to get around the current predicament of no theme colours I created a model in excel which uses a custom function that calculates all the HEX values of an entire excel theme.

This has enabled me to get an entire palette of colours that I can use to format my reports. I've decided to share this with the community, so hopefully, others find it a useful as I do and can utilise it to create amazing looking reports.

To use the tool locate the download available with this post.

Select a theme in excel, either a custom Microsoft one or your own corporate one.

Once selected the HEX values will show for the theme.

From here confirm the theme.

You then input what you would like to call the new theme. Following that a new worksheet will be created, with the name you just input along with the HEX values. These HEX values become hard coded so that it is as easy as a copy and paste over to Power BI.

By using this tool, you are able to get numerous looks for your reports depending on the colours you decide to use. I've found it has sped up my design time on my reports immensely since I've started using it and is a useful work-around until we have some more efficient colour formatting ability within Power BI itself.

For examples on how I use the colours within a selected palette, check out some of my reports on the Partner Showcase (Enterprise DNA).

[vrocca]

I am getting a compile error on the Macro in the workbook:

"The code in this project must be updated for use in 64 bit systems. Please review and update the Declare statements and then mark them with the PtrSafe attribute."

[EnterpriseDNA]

Hi there, sorry I can't replicate this issue on any of my machines, but have made some updates to the code. Can you please advise if it now works with the changes I've made. If not please email me at sam.mckay@enterprisedna.co.nz

Please download file here. http://www.evernote.com/l/AAsSHRti4HFJV4pjTBCoJIM3gDE21f3yunQ/

[pmdci]

If you need to get a list of all of the colours included in the default colour scheme of Office 2007-2010 and 2013-2016, I created a PDF which can be found here. But it only includes the default themes 

If you're looking to colour R charts in Power BI with office-based colours, I created a package for R called Redmonder, which can be found here. Redmonder allows you to select any of the themes included in Office for colouring charts, as well as the default (and only, for now) Power BI one.

Regards,

P. 

[Anonymous]

If you are looking for theme generators that will help your colours to be aesthetically complimentary, I can recommend these couple of sites that may help:

http://paletton.com

https://coolors.co/

Paletton has a great export feature which allows the theme palette to be exported as a multitude of different file types and I also find their Vision Simulation feature useful, especially if designing for colour-blind audiences.

Kind regards,

Andrew Exley

Exposé Data

[vrocca]

 Very cool - Thanks Andrew! 

[Bjoern]

Am I missing something or does this makro only convert a filled cell into a HEX-Value of the colour? Where is the link to Power BI?

[TonyGaul]

I've got the Compile Error in x64 Version ('PtrSafe' attribute required) error too. To fix this on 64 bit installations change the VBA code for all the function declarations
From:
Public Declare Function GetWindowLong
To:
Public Declare PtrSafe Function GetWindowLong

[vrocca]

Thanks @TonyGaul - this fixed it for me as well!

[EnterpriseDNA]

Thanks for the assistance Tony. Chrs, Sam