Current Rank out of Total Indicator

[Community_Admin]

Analytical Need 

In certain scenarios, one would like to show an indicator that presents the ranking of a current selection(filter) out of all the items in the pool.

This allows the viewer to quickly understand how well a particular selection is performing compared to the rest of the possible selection.

One example of a business use-case could be a Sales Team Manager who needs a deep dive picture of a specific Sales Agent.

The Sales Team Manager will have a set of dashboards, starting with an overview of the team, to a drill down on the Sales Agent level.

One of the more important KPIs for the Sales Team Manager could be how well the agent is doing compares to the other team member.

Here is the end result:

Modeling Challenge

How do we perform ranking on the dashboard while making sure it remains dynamic and not pre-created on the ElastiCube?

To achieve ranking, we could pre-create the rank on the ElastiCube for the relevant dimension, yet this will not be dynamically changing on the front end when introducing more dimensions.

For that purpose, we will use the RANK() function.

Solution

Here are the steps:

1. Create a pivot table – This will allow us a glance into the raw data before visualizing it, making sure we are seeing the expected results.

Here is the pivot we will create:

2. Pick the dimension for which the ranking will be calculated for.
3. Perform the ranking using RANK() and apply the ALL() function as a filter measure on the dimension. 

Without applying an ALL() function, we will be getting Rank = 1 for each item in our dimension.

In our example, we are getting the rank in a descending fashion for 'Apple Mac Laptops' based on the Total Revenue.

4. Create another value that returns the total number of items for the selected dimension using ALL() function. This will allow us to compare the ranking of the currently selected item to the total.

 Next, to visualize the data, we will need to pick the right widget type.

As an Indicator type widget allows only values, there will be no comparison between the selected items to the other possibilities in the pool, and the result for that item would be always 1.

Therefore, we need to pick a different widget type with which we can present an indicator.

We will go with BloX (go through the documentation).

BloX will allow us to determine which items from the panel to present the option to flexibly change the design.

Now that we have already created the pivot table, all there is to do is change the widget type to BloX and the panels with the correct calculations will be ready for us to use.

That's it!

 
{
    "style": "",
    "script": "",
    "title": "",
    "showCarousel": true,
    "backgroundImage": "https://images.pexels.com/photos/2340254/pexels-photo-2340254.jpeg?auto=format%2Ccompress&cs=tinysrgb&dpr=1&w=500",
    "body": [
        {
            "type": "Container",
            "items": [
                {
                    "type": "TextBlock",
                    "text": "{panel:Category}",
                    "weight": "bold",
                    "size": "extraLarge",
                    "horizontalAlignment": "center",
                    "color": "white"
                },
                {
                    "type": "TextBlock",
                    "text": "Revenue {panel:Total Revenue}",
                    "size": "medium",
                    "spacing": "medium",
                    "weight": "light",
                    "color": "white",
                    "horizontalAlignment": "center"
                },
                {
                    "type": "Image",
                    "horizontalAlignment": "center",
                    "spacing": "medium",
                    "url": "https://cdn140.picsart.com/261504649036212.png?r1024x1024",
                    "size": "large",
                    "style": {
                        "border-radius": "50%",
                        "border": "4px solid #fff"
                    },
                    "altText": "placeholder"
                },
                {
                    "type": "TextBlock",
                    "horizontalAlignment": "center",
                    "text": "Rank",
                    "size": "medium",
                    "weight": "bold",
                    "color": "white",
                    "spacing": "large"
                },
                {
                    "type": "TextBlock",
                    "horizontalAlignment": "center",
                    "text": "{panel:Rank} / {panel:All Categories}",
                    "size": "samll",
                    "weight": "light",
                    "spacing": "samll",
                    "color": "white"
                }
            ]
        }
    ]
}