Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

MFelix

Think inside the box - Custom visuals using Standard Visuals

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:

 

GAUGE_EXPLANATION.png

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

Gauge_rot.png

 

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.

orignal.png

 

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 2nd marker
  • Yellow 2nd marker
  • Red before marker
  • Red marker
  • Red after marker
  • Unused Area
  • Green before marker
  • Green 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.

Tachomete.png

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:

Backgfournd.pngfont.png

 

As you see you have a Gantt chart created:

Gannt-.png

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

 

 

 

 

 

 

Comments
Anonymous

I always enjoy reading your posts and comments,

Thanks for being a great support @MFelix 

Anonymous

I really love this blog and I also believe in "Everything a custom visual does you can do with the standard visuals".  Make the most of the default visuals rather than reinvent the wheel! Thanks for the Post! 

Hello! 

This is awesome! THanks for sharing.

I had some questions while trying to replicate the gantt chart in my model. Is there any relationship between the calendar table and the task table? If yes, which column the date calendar table relates to? Start date or end date? I downloaded your sample but did find any relationship between them. I dont know if it is a requirement but it is not working for me quite well. 

Hi @renmello,

 

Thank you first of all.

 

There is no relationship between both tables and that is the purpose, you then need to the the columns from the calendar table on your matrix visualization.

Good Afternoon @MFelix,

Hoping you can assist me with my Gantt Chart.  I am tracking train activity in a railyard, hence am using a 24 hour clock (15 minute increments) as my column headers:

rsbin_0-1629314300319.png

If "Release" is 02:45 for the first record, I would like the "1", to show up in the correct time column.

I am unable to get my Measure working for this.  My fields are TrainStatistics[Release] and Dim_Time_Matrix[HHMM].  Really hoping you can help me with this.

Thanks for all you do and best regards,

 

Hi @rsbin ,

 

Believe your issue is regarding the formatting of your data to what you refer you have DIM_Time_Matrix has

[HHMM] is this in the format 0115 that refer to 01:15?

 

What is the format? Text? 

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

Good Morning @MFelix ,  Thank you for the reply.  Mock up data as follows:

TrainStatistics:  Departure and ReleaseRounded are in Time (HH:MM) format.  Project and TrainID are my Row Headers pictured above (with some other ancillary data).

ProjectTask(TrainID)DepartureReleaseRounded
7/31 - 8/11999:152:45
8/1 - 8/21999:152:45
8/2 - 8/31999:153:00
8/3 - 8/41999:151:30
8/4 - 8/51999:152:00

 

DIM_Time_Matrix:  (Only showed partial table.  Full table has 24 hours in 15 minute increments)

I am working with an SSAS model.  So format is Time  (HH:MM).  Minute column is Whole Number.

Am using ClockTime_HH and Minute as my Column Headers.

ClockTime_HHMMClockTime_HHMinute
0:000:000
0:150:0015
0:300:0030
0:450:0045
1:001:000
1:151:0015
1:301:0030
1:451:0045
2:002:000
2:152:0015
2:302:0030
2:452:0045
3:003:000
3:153:0015
3:303:0030
3:453:0045
4:004:000
4:154:0015
4:304:0030
4:454:0045
5:005:000
5:155:0015
5:305:0030
5:455:0045

 

I have more detailed message on the Help Board under the topic: 

Matrix Visual as a Gantt Chart - by Hour  (apologies as I am not sure how to paste a direct link)
If changing formats will help solve the problem...I am completely open to that.
 
Thank you again for offering to assist.  Let me know if any further details are required. 

Hi @rsbin 

 

In this case you need to use the following measure:

Train Time = 
IF (
    (
        MAX ( DIM_Time_Matrix[ClockTime_HHMM] ) >= MAX ( TrainStatistics[Departure] )
           || MAX ( DIM_Time_Matrix[ClockTime_HHMM]) <= MAX ( TrainStatistics[ReleaseRounded] )
    ),
    
    1
)

 

Final result below:

MFelix_0-1629467453532.png

Check this wetransfer link with the file.

@MFelix,

Thank you so much, this is very close, but I suppose I wasn't perfectly clear in my need.

Release is one event...so I need a 1 or "R" in this time slot.

Departure is a separate event, so I need a 1 or "D" in this time slot. 

I do not need 1's in between these two events.

Eventually I can use icons or conditional format fill to highlight or differentiate these two separate events.  Can I impose upon you to modify this Measure please?

Edit:  I think I got it.

Train Time = 
IF (
    (
       -- MAX ( DIM_Time_Matrix[ClockTime_HHMM] ) >= MAX ( TrainStatistics[Departure] )
            MAX ( DIM_Time_Matrix[ClockTime_HHMM]) = MAX ( TrainStatistics[ReleaseRounded] )
    ),
    
    "R"
)

rsbin_0-1629468852612.png

 

Just to verify, there should be no relationship between these two tables....correct?  Now I should be able to get this to work in my file.

You beat me to it....just saw your response as well.

 

Thanks again so very much!

Hi @rsbin 

 

Then you should try this

Train Time = 
SWITCH(MAX ( DIM_Time_Matrix[ClockTime_HHMM]),
 MAX ( TrainStatistics[ReleaseRounded] ), "R",
  MAX ( TrainStatistics[Departure] ) , "D"           
)
)

MFelix_0-1629468911563.png

 

@MFelix,

One more issue I need to resolve for.  Perfect world, I need my Column Headers to start at 12:00 PM.  Most of the yard activity is during overnight hours.

I tried using an index where 12:00 PM is 1 and 11:45 AM is 96.  (24 x 4 time segments) thinking I could sort my column header.

But the Matrix won't give me that option.  Any thoughts or suggestions on how I might accomplish this?

Again, very appreciative of your help!

Hi @rsbin ,

 

On the matrix one option can be to add the index column on the columns and then add a shape with a white background to hide it, bottom image is with the image on top:

 

MFelix_0-1629473793709.png

 

@MFelix,

Thank you for that suggestion.

And again a big thank you for helping me through this issue.

Best Regards!

nice work