Measure contribution in a chart

One of the frequent questions we have to resolve is the contribution of a given measure in a chart and especially a pie chart.

The idea is to display in a chart the customers where revenue contribute to 70% of the total, then the customers where revenue contribute to 20% of the total and finally the customers where revenue contribute to 10% of the total.

Then we need to create a segment for each population, and in the chart count the number of customers for each segment.

Here is the dataset used as an example:

To do it I create a variable [Running Pct Revenue] to compute a running percentage on the measures and the formula is the following:

=(RunningSum([Revenue]) / Sum([Revenue] In Report) * 100)

We can also use the RunningPercentage function.

Now I add the new variable in the table.

Last I create the variable [Customer Group on Customer] to populate the different segments of the customers and the formula is the following:

=(If [Running Pct Revenue]  <=  70 Then “Group 70%”

Else If [Running Pct Revenue]  > 70 And [Running Pct Revenue]   <= 90 Then “Group 20%”

Else “Group 10 %”) In ([Customer])

And I add this new variable in the table.

Last I create the variable [Count Customer] to compute the number of customers: =Count([Customer])

And now I can create the chart containing the measure [Count Customer] and the dimension [Customer Group on Customer]

That’s good but there is a major issue! When we compute the contribution we want of course the customers having the highest revenue in the 70% segment then the less high in the 20% and the smallest revenue in the 10%.

But in our calculation the segments are computed based on the order of the dataset. In my example, the running percentage and then the segments are based on the alphabetical order of customer names.

Now if we sort the table on the Revenue is descending order and we change of the variable like this:

=(If [Running Pct Revenue]  <=  70 In ([Customer]) Then “Group 70%”

Else If [Running Pct Revenue] In ([Customer]) > 70 And [Running Pct Revenue] In ([Customer]) <= 90 Then “Group 20%”

Else “Group 10 %”)

The table shows the good results!

But not the chart!

The reason is that in the chart there is NO Customer dimension and NO sort.

So it is not really difficult to resolve in a table but it’s more complex to do it in a chart.

Measure contribution in a chart

As explained above we need the best revenue first and the worst at the end.

So rather than to work on the customer dimension that is alphabetically sorted, we need to work on the measure: Revenue.

But Revenue is a measure, so we need to turn it into a dimension.

I create a new variable [Revenue Dim] with the following formula:

=FormatNumber([Revenue];”000000000″)

Because we will use the measure as pivot of our formula and this measure will be automatically sorted by Web Intelligence, we need to ensure that the value have to be correctly ordered this is why the FormatNumber function add enough zeroes on the left of the new created dimension.

I create a new table including the new created variable as following:

I sorted the table on the variable [Revenue Dim] in descending order for better a reading.

Finally, I create the variable [Customer Group on Revenue] to populate the different segments of the customers and the formula is the following:

=(If [Running Pct Revenue]  <  10 Then “Group 10%”

Else If [Running Pct Revenue]  >= 10 And [Running Pct Revenue]  < 30 Then “Group 20%”

Else “Group 70 %”) In ([Revenue Dim])

I replaced [Customer] by [Revenue Dim].

And I created the segments in a reverse way because the default Web Intelligence sort is always ascending and there is no way to specify the sort in the formula.

The segments are correctly created in the table ……

….. And in the Chart!

That’s all. We can now use this technique to Create multiple variance of charts.

You can download the Web Intelligence report attached to that publication.

Didier MAZOUE