Sign in or start a free trial to avail of this feature.
2. Calculating Market Share
Often in business, you want to see what regions, products or sales people are generating the most revenue. Learn how to track this market share over time in this lesson.
- The market share for a subregion is the revenue for that region divided by the revenue for all sub-regions
- Understanding filter contexts is particularly important when creating formulas for measures like this
- We demonstrate market share for subregions, but you could create similar measures for sales people, products etc.
- Creating a date hierarchy allows us to easily see data at different levels: yearly, monthly etc.
- The Divide function performs simple division but does not produce an error if the denominator is zero
- Alt N, SZ, C – Create PivotChart
- Alt JC, C – Change chart type
- Alt H, W – Enter diagram view (in Power Pivot)
- Alt B, F – Create measure
- Alt H, D2 – Enter data view (in Power Pivot)
- Alt H, PE – Format as Percentage
- Alt H, 9 – Reduce decimal places
Calculating market shares is a common task you'll come across in various business scenarios. In this lesson, we'll see how to calculate market shares in DAX using a simple function.
In the previous lesson, we looked at trends in revenue by region.
This time, we'll look at revenue by subregion.
We'll create a pivot chart.
Put it on a new sheet.
And change the chart type to a line chart.
We'll then expand the sales table on the right side of the screen, add revenue to the values area, date to the axis, subregion to the legend, and remove date month.
As with the previous lesson, this chart is impossible to read due to the frequency of the data.
In order to deal with this issue, we'll analyze our data at a monthly level instead of daily.
To accomplish this, we'll need to create a hierarchy in the dates table.
We'll navigate to the data model, and look at the dates table in diagram view.
We'll then select the create hierarchy button at the top of the table.
And call it date hierarchy.
We'll add three levels to the hierarchy.
First year, then month name, and finally day.
We'll now return to the Excel window. We'll remove the date column from the line chart, and replace it with the date hierarchy from the dates table.
We're currently at the top level of the hierarchy, which is year. We can move through the hierarchy using the plus and minus buttons in the corner of the chart.
We'll press the plus button and see that we're now at the next level of the hierarchy, which is month.
The chart now shows monthly revenue for each of the subregions in the data set, which is what we wanted.
We can clearly see that there are three subregions which contribute more revenue than any of the other subregions. East North Central, Middle Atlantic, and South Atlantic.
However, it's not easy to identify the importance of each subregion. To determine this, we'll compute the market share for each subregion.
We'll create a new measure, and call it market share.
The market share of a subregion will simply be the sum of revenue for that subregion divided by the sum of revenue for the entire data set. We'll use the divide function.
The numerator will be the sum of revenue.
The line chart provides a filter context for the subregion field, so we don't need to include it in the numerator.
We'll then enter a calculate function for the denominator, and calculate the sum of revenue with a filter of the all function applied to the subregion field.
We'll then close the formula and select okay to create the measure.
The advantage of using the divide function is that it does not produce an error if the denominator is zero.
This will not be a problem here, but it's good practice to use the divide function when creating DAX formulas.
We'll now select our line chart, remove the revenue field, and replace it with a market share measure.
You'll notice the chart looks very familiar.
In fact, the lines are the same as before. Only the vertical axis has changed.
It now shoes the market share of a subregion in a particular month.
This axis is currently formatted as a decimal, however market shares are best formatted as a percentage.
To address this, we'll navigate to the power pivot window, view the sales table in data view, and select the market share measure from the calculation area.
We'll change the formatting of the measure to a percentage with a keyboard shortcut, Alt H P E.
Note that the measure currently has two decimal places.
Let's reduce this to zero by using the shortcut Alt H 9 twice.
We'll now return to Excel and see that the axis on our chart is formatted as a percentage.
A market share graph makes it very easy to see which subregions are most important and to measure just how much of a contribution they make to total revenue.
This chart tells us that the top three regions each account for at least 15% of revenue in every month of the data set.
Taken together, the top three subregions account for over half the revenue generated by the company in any given month.
As we've seen in this lesson, market shares can easily be calculated in DAX using the divide function.
However, you do need some understanding of the filter context to know exactly how to create this measure. This is typical of DAX, and we'll see many similar situations in the rest of the course.