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.
Lesson Goal (00:09)
The goal of this lesson is to learn how to calculate market shares in DAX using a simple function.
Charting Revenue by Subregion (00:16)
In order to understand revenue by subregion, we create a line chart showing revenue over time with a separate line for each subregion. This line chart is too crowded, because sales data is recorded on a daily basis. In this situation, one way of improving the chart is by analyzing monthly data instead of daily data. We can do this by creating a date hierarchy in the data model.
We can create a hierarchy in Diagram View in the Power Pivot window. We create a hierarchy by selecting the button in the table header, then drag fields into the hierarchy. In our case, we create a date hierarchy in our date table containing year, month, and day fields. If the hierarchy contains non-numeric fields, such as month names, you may need to sort them by a corresponding numeric field to ensure they appear in the right order in the Pivot Chart later on.
When we add a hierarchy to a Pivot Chart, we can move between levels of the hierarchy using the plus and minus signs in the bottom corner. We use our hierarchy to create a Pivot Chart of revenue by month, with a separate line for each subregion.
Creating a Market Share Measure (02:34)
The market share for a subregion is the percentage of revenue generated by that subregion. We can calculate it by dividing the total revenue for a subregion by the total revenue for all subregions. We can do this in DAX using a simple measure, which uses the DIVIDE function. DIVIDE performs division, but doesn’t return an error if the denominator is zero.
In our measure, the numerator is the sum of revenue, as the filter context of the Pivot Chart provides a filter for an individual subregion. The denominator uses CALCULATE and ALL to calculate the sum of revenue for all subregions.
Using the Market Share in a Chart (03:49)
When we add the market share measure to a chart, the chart has the same shape as a chart of revenue over time, only the vertical axis changes. By default, a market share measure is formatted as a decimal number. To format it as a percentage, we can change the data type of the measure from data view in the Power Pivot window.
Once we’ve created a market share chart, we can derive insights from it. For example, our chart tells us that three leading subregions have larger market shares than the other subregions. These leading subregions contribute more than half of the company’s total revenue. This tells us that these subregions are particularly important to the company.
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.