Although they are very easy to import from the market place and provide good insights there are some limitations:
A custom visual is added to a specific report when imported;
New versions of customs visuals are not updated automatically;
Custom visuals may stop working with the last version of PBI;
They can take a lot of time to load in a report;
During my use of PBI this last limitations was particularly visible when I was contacted by a colleague at work with a KPI dashboard that had around 10 items, these items were calculated for monthly information of the last 4 years with 5 inputs that allowed us to make all sort of KPI (around 400 cells with information).
When looking at the model there was no apparent reason for a change of MTD to YTD to take 5 minutes to update, but in fact, that was the case. Also looking at the way everything was set up I didn’t see anything that could cause the delay because as referred there were only 10 cards and a couple of text boxes and logo from the company.
While investigating I realized that although the cards look pretty standard (no special colors or items) the report was created with Card with States by OKViz.
This is a great visual but, in this case, we weren’t using the full potential of it, as said there were no colors, no trend lines and it just looked the standard card available on PBI. We then decided to replace all those cards to the standards ones and then the report started to take a few seconds to show the data, so the limitation on custom visuals was clear to me.
Although this is a limitation, I also acknowledge that there is some visualization that cannot be made without a custom visual or an R visual, however, while making my contributions on the community I started to see recurrent requests and some of them were responded (even by me) check the custom visuals gallery.
A few weeks ago, I started to remember a blog post I read (sorry for not remembering what is the link to it) and especially a phrase that got stuck in the back of my mind, and that I use for my reports. Making a free paraphrasing it was something like:
Everything a custom visual do you can do with the standard visuals.
Although this sentence is not completely true (examples: Infographic Designer or Synoptic Panel by OKViz – one of my favorite ones), I know there is a lot we can do if we know how PBI visuals are set up and if we make the right DAX calculations or query transformations.
The Though process
Being in the PBI community almost since the beginning, I have seen a lot of people asking several times for ways to present visuals that are not in the standard visuals from them I have selected 3 although one is not quite a visual itself:
Rotating a gauge visual
Rotating gauge visual
As referred, this is not exactly a different visual from the standard people only want to rotate it and present it from a different perspective.
When looking at the visual we see that basically the gauge visual is half of a Pie Chart / Donut Chart so using some out of the box thinking we can use the Donut Chart to rotate our gauge.
First of all, we need to understand how the Donut Chart works (this is also valid for the next visual the Tachometer) main principles are:
Donut Chart begins in the Top middle part of the visual
They are sorted clockwise
These two principles are visible in the images below:
As you can see when sorting ascending (1, 2, 3, 4) the 1 is on the upper right corner and the 4 is on the up left corner while descending they switch places.
Base on this knowledge we only have to select how we want to have our rotating chart showing.
Assembling the rotated gauge
To simplify my values for this chart are defined by a parameter table with formatting as a percentage and maximum value of 100% so I can go from 0% to 100%.
We need to create 3 measures:
Percentage Used – Area of the chart that will be colored:
Percentage Used = [Incremental Value]/2 * 100
Since the Donut Chart is with a base of 100% and the maximum value of my parameter is also 100% but I only want to have half of my chart with a color I divide the value of the Parameter by two so basically making the 0 bases to be the 50%
Blank area – to determine the side for which the chart will be rotated simple measure with a fixed value
Blank area = 50
As referred I want to have half of my chart blank, so I make it a base of 50.
Filling Area – to determine the side for which the chart will be rotated simple measure with a fixed value
Filling Area = [Blank Area] - [Percentage Used]
This will be simply the difference between the other two measures.
Now we need to place the measures in the correct order on the visual if you want to have a gauge with the values on the right and filling down sort them like this:
Know you can play around with the location of measure and you will get your gauge filling in different ways.
You can also add a card with the selected value and it will appear that your gauge rotated and it’s a custom visual.
Adding to this if you want to have your gauge head down you need to divide your blank space into 2 and then place the two measures on top an bottom.
This visual appears a lot of times in the request I see people like to present different color areas to represent intervals of acceptance of value.
But if we know how visual work we can replicate this, so referring to the way the Donut Chart works we can replicate this visual.
The assumption behind the tachometer based on donut chart:
3 color areas: Green, Yellow, Red
All 3 areas have the following scheme (simplification for example purposes):
Below 33,3% Green
Above 33,3% and below 66,6% Yellow
Above 66,6% Red
Assembling the Tachometer
If we start to think it over like a puzzle we see that we will not only need the 4
We need to break it down on parts looking at the way:
8 Areas (see above)
2 for red (above and below marker)
2 for green (above and below marker)
4 for yellow (since the donut chart sort clockwise from top)
One marker per each color area
1 blank area
Measure for each area:
Unused Area = 50
Green Marker =
VAR Fillquantity = [Unused Area] / 3
IF ( [Incremental Value] / 2 * 100 >= 50 / 3; BLANK (); 1 )
// if Incremental value is greater than 33% the marker is on yellow or red so on green will be blank
Green after marker =
[Green Marker] = BLANK ();
[Incremental Value] = 0; //Validation of 0 in order to place marker on beginning of gauge chart
50/3 - 1; // when Gauge is at zero
50/3 - [Green Marker] - [Green fill before marker] // Value after marker
Green before marker =
[Green Marker] = BLANK ();
50/6 + ( [Incremental Value] / 2 * 100 - ( 50/6 ) ) < 0; //Since incremental value is at a base of 100 and chart used is at a base of 50 incremental value needs to be divided by 2 when we have 10% on Incremental value this matches 5% on the gauge chart. Number of color is 3 but since we have a marker in the middle of the color value needs to be 1/6 of the area an not 1/3
50/6 + ( [Incremental Value] / 2 * 100 - ( 50/6 ) - [Green Marker] )
Red Marker =
[Green Marker] = BLANK ()
&& [yellow 1st Marker] = BLANK ()
&& [Yellow 2nd marker] = BLANK ();
Red Before Marker =
[Red Marker] = BLANK ();
50 / 3;
50 / 3 + ( ( [Incremental Value] * 100 - 100 ) / 2 - 1 ) < 0;
50 / 3 + ( ( [Incremental Value] * 100 - 100 ) / 2 - 1 )
Red after marker =
IF ( [Red Marker] = BLANK (); 0; 50 / 3 - [Red Before Marker] - [Red Marker] )
Yellow 1st Marker =
[Green Marker] = BLANK ();
Incremental[Incremental Value] <= 50 / 6
&& Incremental[Incremental Value] < 0,5;
IF ( Incremental[Incremental Value] / 2 * 100 <= 50 / 1,5; 1; 0 );
Yellow after 1st marker =
[yellow 1st Marker] = BLANK ();
50 / 6 - ( ( [Incremental Value] * 100 - 100 / 3 ) / 2 ) + [yellow 1st Marker] > 50 / 6;
50 / 6 - 1;
50 / 6 - ( ( [Incremental Value] * 100 - 100 / 3 ) / 2 )
Yellow after 2nd marker =
[Yellow before 2nd marker] < 0;
50 / 6 - [Yellow 2nd marker];
50 / 6 - [Yellow before 2nd marker] - [Yellow 2nd marker]
Yellow before 1st marker =
50/6 - [Yellow after 1st marker] - [yellow 1st Marker]
Yellow before 2nd marker =
[Yellow 2nd marker] = BLANK ();
50 / 6;
50 / 6 - ( 100 / 3 - ( [Incremental Value] * 100 ) / 2 ) - 1
Now the only thing is to set up in the correct order (consider the clockwise rotation):
Yellow before 2nd marker
Yellow 2nd marker
Red before marker
Red after marker
Green before marker
Green after marker
Yellow before 1st marker
Yellow 1st marker
Yellow after 1st marker
On the images below you can see the position of the marker is correct when comparing with the default gauge visual from PBI.
This visual is very requested when talking about project management.
But as in the previous visual, we do some setup we can go without the custom visuals.
Assembling the Gantt
Area to be marked as filled
Conditional formatting of Matrix table
This is a simple Gantt, so I only create a single measure:
Usage User =
MAX ( 'calendar'[Date] ) < MAX ( 'Gantt Chart'[Start Date] )
|| MAX ( 'calendar'[Date] ) > MAX ( 'Gantt Chart'[End Date] )
Then we just need to create the conditional formatting on a matrix. Setup you matrix as below:
Now drill down to the lowest level in the columns and conditional format the values (Background and Font Color) as below:
As you see you have a Gantt chart created:
Now you can format Colum and rows headers at will.
As you can see if we know how the visuals and the DAX / M language works on PBI we can do a lot of customization without the need of having additional visuals.
However, with this post, I’m not referring that we should not use the custom visuals since it’s also a great advantage in Power BI to add different ways of doing things and make our data really stand out and tell a story.
Also, the measures presented here can be optimized with the use of variables and/or dynamic parameter, however, they were just made for proof of concept so some errors or duplication is present on the model.
See the full working PBIX file on the Data Stories Gallery and you also can download the file attached, and if you have any additional suggestions please feel free to share them.