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:

Mesure contribution - 1.png

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.

Mesure contribution - 2.png

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.

Mesure contribution - 3.png

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]

Mesure contribution - 4.png

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!

Mesure contribution - 5.png

But not the chart!

Mesure contribution - 6.png

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:

Mesure contribution - 7.png

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

Mesure contribution - 8.png

….. And in the Chart!

Mesure contribution - 9.png

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

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Dar vad

    Hi Didier,

    excellent document. solved my problem.

    one thing I noticed is in last table column customer group on customer, Group 70% is shown even the running pct revenue is higher than 70.

    kind regards,

    Dar

    (0) 
    1. Didier MAZOUE Post author

      Hi Dar,

      I updated the document because there were an error in the condition of the segments variable. The correct formula is:

      =(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])

      Didier

      (0) 

Leave a Reply