Think inside the box - Custom visuals using Standard Visuals

[MFelix]

The context

Looking at the evolution of Power BI during the last three and a half years, one thing that really is visible is the number of custom visuals and the variety of solutions that they let us solve.

There is from simple table formatting options to advance analytics with forecasting neural networking.

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;
• …

 

The Story

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
• Tachometer
• Gantt Chart

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:

1. Percentage Used
2. Filling Area
3. Blank Area

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.

Tachometer

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.

You have the  Tachometer by Annik Ink on the custom visuals (https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380937?tab=Overview)

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:

• 1 marker
• 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)
• 3 markers
  • One marker per each color area
• 1 blank area

Measure for each area:

Unused Area = 50
==========================================
Green Marker =
VAR Fillquantity = [Unused Area] / 3
RETURN
    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 =
    IF (
        [Green Marker] = BLANK ();
        50/3;
        IF (
            [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 =
IF (
    [Green Marker] = BLANK ();
    0;
    IF (
        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
        0;
        50/6 + ( [Incremental Value] / 2 * 100 - ( 50/6 ) - [Green Marker] )
    )
)
==========================================
Red Marker =
IF (
    [Green Marker] = BLANK ()
        && [yellow 1st Marker] = BLANK ()
        && [Yellow 2nd marker] = BLANK ();
    1;
    0
)
==========================================
Red Before Marker =
IF (
    [Red Marker] = BLANK ();
    50 / 3;
    IF (
        50 / 3 + ( ( [Incremental Value] * 100 - 100 ) / 2 - 1 ) < 0;
        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 =
IF (
    [Green Marker] = BLANK ();
    IF (
        Incremental[Incremental Value] <= 50 / 6
            && Incremental[Incremental Value] < 0,5;
        IF ( Incremental[Incremental Value] / 2 * 100 <= 50 / 1,5; 1; 0 );
        BLANK ()
    )
)
==========================================
Yellow after 1st marker =
IF (
    [yellow 1st Marker] = BLANK ();
    0;
    IF (
        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 =
IF (
    [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 =
IF (
    [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 2^nd marker
• Yellow 2^nd marker
• Red before marker
• Red marker
• Red after marker
• Unused Area
• Green before marker
• Green marker
• Green after marker
• Yellow before 1^st marker
• Yellow 1^st marker
• Yellow after 1^st marker

On the images below you can see the position of the marker is correct when comparing with the default gauge visual from PBI.

Gantt Chart

This visual is very requested when talking about project management.

You have two available custom visuals:

Gantt by Microsoft Corporation  (https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380765?tab=Overview)

Gantt Chart by MAQ Software (https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381364?tab=Overview)

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 =
IF (
    (
        MAX ( 'calendar'[Date] ) < MAX ( 'Gantt Chart'[Start Date] )
            || MAX ( 'calendar'[Date] ) > MAX ( 'Gantt Chart'[End Date] )
    );
    BLANK ();
    1
)

Then we just need to create the conditional formatting on a matrix. Setup you matrix as below:

Rows:

• Task
• User

Columns:

• Year
• Month Name
• Day

Values:

• Usage User

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.

Final words

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.

Regards,

MFelix

CustomVisuals.pbix