Sales revenue contribution with input controls
I wrote the document Measure contribution in a chart that explains how to display in a chart the customers where sales revenue contributes to a given percentage of the total.
The document also details how computing the contribution for the customers having the highest sales revenue.
In this document I provides additional usages of measure contribution in a chart.
Here is an excerpt of the dataset I am using.
In this dataset there is an additional dimension (Year) that was not present in the dataset described in Measure contribution in a chart
The variables to create are similar to the previous document except that we have to take into account the Year dimension.
So now, the formula to compute number of customers is the following:
=Count([Customer]) ForEach ([Year])
As in the previous document I created segments of customers but I added an input control to change the contribution such as 80/20, 70/30, 55/45, etc.
To do so, I created a variable named “Contribution %” with the formula: =80
Then I created an input control based on this variable with the following characteristics:
- Type: Simple Slider
- Minimum value: 0
- Maximum value: 100
- Increment: 1
- Default value: 80
I also changed the formula of “Customer Group” variable to take into account the “Contribution %”:
=(If [Running Pct Revenue] <= (100 – [Contribution %]) Then “Group ” + FormatNumber(100 – [Contribution %];”0%”)
Else “Group ” + FormatNumber([Contribution %];”0%”) ) In ([Revenue Dim])
So the segments and the segments name will change according to “Contribution %” value.
I also add a title in the chart to also take into account “Contribution %” value:
=”Sales Revenue contribution: ” + FormatNumber([Contribution %];”0″) + “/” + FormatNumber(100-[Contribution %];”0″)
So here are different chart displays after having changed the Contribution %” value.
Another way to show the contribution is to display the customer name for one of the segments and display “Other” for the other segment.
So I created a new variable named “Customer Group Name” with the following formula:
=(If [Running Pct Revenue] <= (100 – [Contribution %]) Then “Other”
Else [Customer]) In ([Revenue Dim])
And last, I added the number of customers belonging to the “Other” segment.
So I created another variable named “Customer Name” with the following formula:
=If [Customer Group Name] = “Other” Then “Other (” + [Count Customer] Where ([Customer Group Name] = “Other”) In ([Year]) + “)” Else [Customer Group Name]
Now here are some chart screenshots with different values of “Contribution %”.
You can download the Web Intelligence report attached to that publication.