Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
545 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Conditionally formatting a Percent of grand total column in Power BI |
---|
Although Power BI allows you to display values as a percent of the grand total, you can't conditionally format this directly. Fortunately, you can use a measure to solve the problem. |
Power BI allows you to display your results as a percentage of the grand total using the Show value as option in a field's drop down menu.
Here the run time minutes are being displayed as a percentage of the overall total run time.
Unfortunately, if you try to conditionally format this column, the percent grand total field won't appear as an option in the list and conditional formats using any other fields may not be applied accurately.
The field we based our percent grand total column on is available but not the one with the values that we want to conditionally format.
If you want a percent of grand total column that you can conditionally format, you first need to create it as a measure.
Right click on the table you want to create the measure in and choose New measure.
This option will open the formula toolbar underneath the ribbon.
Enter a name for the measure followed by the formula to calculate the value as a percentage of the grand total.
The Divide function helps avoid errors caused from dividing by 0.
Format the measure as a percentage using the Measure tools tab on the ribbon and add it to the visual.
If the last column hadn't been formatted as a percentage, it would be displaying as a decimal number.
Finally, apply conditional formatting from the Columns field well and you will be able to base the conditional formatting on the measure that you created.
When setting your rules, you will need to base them on the underlying decimal value and not the percentage values displayed in the visual.
Conditional formatting will now behave in the way you would expect it to.
Our measure column on the right applies the conditional formatting rules correctly compares to the inbuilt percentage column on the left.
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.