Pie Charts: Using a Pie Chart to replace multiple Gauge Charts

    Hi all,

     

    I recently came across an issue with a client requiring multiple Gauge charts on screen but didn't have the real-estate for them to look any way decent. Below is what they had in the past, three line gauges, representing Clients vs. Potential Clients.

    Client Level Bars.PNGOriginally they just wanted to show the amount of Clients they had at each level (Gold, Silver, Bronze) vs the amount of Clients they could have if they won their contracts. I gave them three line gauges for their dashboard, displaying both figures (won & potentials) for each, along with the percentage of business won. This worked fine for a while but as the list of information to be displayed on the dashboard grew, the screen real-estate shrunk. As they liked the idea of seeing a visual representation of how close they were to their targets, I revisited my original approach and tried grouping all three gauges so that I could merge them into a single pie chart. Below is the result along with how I accomplished it.

    Client Level Pie.PNGThis particular chart is divided into 3 equal segments, has an overall percentage of their KPI in the middle (text box), and displays the total amounts to the right (text box). The number of Clients are displayed in the pie chart and the list of Potential Clients are visible in a pop-up text when hovered over.

     

    Creating the Pie Chart

     

    • General Tab > Chart Type > Pie Chart
    • Dimensions Tab > Used Dimensions > None (leave this blank)
    • Expressions Tab > Create 6 expressions to calculate the length or each segment (3 for Clients, 3 for Potential Clients)

    Expressions.PNGFor each expression, you will need two calculations, one to display the number that users are going to see in the chart and the other to work out the segment's length in relation to the overall picture. These are then combined using the DUAL() function. Below shows the formula for the Bronze Won segment that shows Clients:

     

    Dual(
      Count({<Flag.Client={1}, Client.Type={'Bronze'}>} ClientID),
      Count({<Flag.Client={1}, Client.Type={'Bronze'}>} ClientID)/Count({<Client.Type={'Bronze'}>} ClientID)
    )
    
    
    
    
    
    
    

     

    The top expression will show the number of Bronze Clients (67 in the chart above). The bottom expression creates the bar/segment by working our the % of clients won out of the overall amount. Similarly, the below expression shows the formula for the Bronze Potential segment that shows Potential Clients:

     

    Dual(
      Count({<Flag.Client={0}, Client.Type={'Bronze'}>} ClientID),
      Count({<Flag.Client={0}, Client.Type={'Bronze'}>} ClientID)/Count({<Client.Type={'Bronze'}>} ClientID)
    )
    
    
    
    
    
    
    

     

    By creating the length as a %, the Won + Potential for each level will always add up to 100% and create the same size allocation for each group, no matter what the actual figure is. In the example above, the pie chart represents a total of 300% as there are 3 groups. It looks great if using 2 or 4 groups but wouldn't advise going past 4 (400%) as the data will get harder to see.


    Repeat this process for Silver & Gold and you will have created your Pie-Gauge-Combo-Chart (the name is a work in progress!). From here you can chose a style that works best for your, allocated the appropriate colours, and control which of the values you wish to see in the chart by checking the Values on Data Points option on the Expressions tab.

     

    Let me know what you think of my creation/abomination in the comments below!

     

    UPDATE: I've attached a quick example of the chart as per request