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 salespeople 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 salespeople, products etc.
- Using the date hierarchy allows us to easily see data at different levels: yearly, monthly etc.
- To access the Date hierarchy we must use the Date field from the Dates table
- The Divide function performs simple division but does not produce an error if the denominator is zero
Calculating market share is a common task you'll come across in various business situations.
Market share can refer to any situation where you have a field with a distinct set of values, and you want to see how much of a contribution each individual makes to the whole. For example, if your company produces many products, you might want to know the share of each product in terms of total sales. If you manage a sales team, you might want to know the percentage of revenue contributed by each salesperson. In this lesson, we'll calculate the market share of each sub-region using a DAX formula. In the previous lesson, we looked at trends in revenue by region. This time, we'll look at revenue by sub-region. We'll create a line chart and again expand it to cover most of the canvas. We'll then navigate to the sales table, add revenue to the values well, date to the access well, and sub-region to the legend well. As in the previous lesson, this chart is impossible to read, due to the frequency of the data. You might think that we can solve this problem by using the date hierarchy to view the chart at the monthly or quarterly level. However, if we select the arrow next to the date field, we can see that we do not have the option to switch to a date hierarchy.
The reason for this issue is that the date column from the sales table is used in a relationship with the date column of the data model's dates table. In order to use a date hierarchy, we must switch to using the date column from the date's table. We'll remove the date column from the line chart and replace it with a date column from the date's table. We now have access to the full date hierarchy.
As daily analysis is not useful here, we'll delete the day field from the hierarchy. We'll then expand down to the monthly level by clicking the branch arrows in the top left of the chart. Our chart now shows monthly revenue for each of the sub-regions in the data set. We can clearly see that the east, north-central, middle Atlantic, and south Atlantic sub-regions contribute more revenue than the others. However, it's not easy to identify exactly how important each sub-region is to the company. To determine this, we'll compute the market share for each sub-region. We'll create a new measure and call it Market Share.
The market share of a sub-region is the sum of revenue for that sub-region divided by the sum of revenue for the entire data set. We'll use a function called Divide to do the division. This function takes two arguments: a numerator and a denominator.
The numerator is just the sum of revenue.
When added to the line chart, this will be the revenue for an individual sub-region in an individual month. The line chart provides a filter context for the sub-region field so we don't need to include it in the numerator. For the denominator, we'll use a calculate function. We'll enter a sum of revenue for the expression and apply an all function to the sub-region field as the filter.
This will calculate the revenue for all sub-regions in an individual month. You might wonder why we're using a function to do our division here.
The advantage of Divide is that it does not produce an error if the denominator is zero. This isn't a problem here, but it's good practice to use the Divide function when you're doing division in DAX. We'll press enter to create the measure. We'll select our line chart, remove the revenue field, and replace it with Market Share. Note that the chart retains the same shape as before. However, the Y axis has changed.
It now shows the market share of a sub-region in a particular month.
This axis is currently formatted as decimal. However, market shares are best formatted as a percentage. To change this, we'll select Market Share in the fields list, select the modeling tab, and format the measure as a percentage. The axis now shows us the percentage of market share for each sub-region. This graph makes it very easy to see which sub-regions are most important, while also measuring just how much of a contribution they make to total revenue. The chart tells us that the top three sub-regions each account for at least 15% of revenue in every month of the data set. Further, the top two sub-regions generally account for above 20% each. Taken together, the top three sub-regions account for over half the revenue generated by the company in any given month. As we can see, market share is easy to determine through a simple division measure. However, as we saw in this lesson, you do need some knowledge of the filter context to understand exactly how to create the measure. This is common for many DAX formulas and is a recurring theme in the rest of this course.