Sign in or start a free trial to avail of this feature.
10. Comparing Sales by Region
In this lesson, we will look at creating multiple charts in Power Pivot. With simple measures, we will create charts for each region that provide a quick overview of sales trends.
Creating a chart of revenue for a single region
- We can use a simple measure to calculate the proportion of revenue generated by a single region, and then chart this measure over a full year
- We add data labels as each chart will have a different scale by default
Creating multiple charts
- We cannot create multiple charts in a single action in Power Pivot
- However, we can create a single chart, for the Midwest region, and create copies of it for the other regions
- We then create a separate measure for each chart by slightly modifying the initial measure for the Midwest region
- Alt B, F – Create new measure
- Alt N, SZ, C – Create PivotChart
- Alt JC, C – Change chart type
- Alt H, S, O – Sort chart from largest to smallest
In this final lesson on DAX, we'll compare sales revenue across all four geographical regions in our data set. Our goal in this lesson is to create a series of charts comparing revenue share for each region over time.
Unfortunately, Power Pivot does not currently allow you to create multiple charts at once. However, we'll learn how to create these four charts by using a single measure for each one.
We'll first create a chart showing revenue for the Midwest region.
To accomplish this, we'll create a measure calculating the proportion of revenue generated in that region.
We'll create a new measure, call it Revenue Midwest Percent and enter a divide function.
The numerator of this divide function will contain a calculate function.
The expression will be the sum of revenue and the filter condition will be that the region field equals Midwest.
The denominator of the divide will be the sum of revenue.
We'll close the formula, set the category as Number, the format as Percentage, the decimal places to zero, and then select Ok to create the measure.
Next, we'll create a pivot chart to illustrate this measure. We'll create the new pivot chart, put it on a new sheet, navigate to the sales table, add the Revenue Midwest Percent measure, navigate to the Dates table, and add the Date Hierarchy.
We'll expand down through the hierarchy and look at revenue by month.
Next, we'll change the chart type to a bar chart.
We'll then select the vertical axis and change assort order with Alt, H, S, O.
This chart shows us the percentage of revenue generated by the Midwest region for each month of our data set.
Let's format the chart and add some data labels. We'll right-click any bar and select Add Data Labels.
Next, we'll title the chart, Midwest Revenue.
Finally, to save space, we'll right-click the Date Hierarchy button, and choose to Hide All Field Buttons on this chart.
The other three charts will be similar. So we'll copy this chart and replace the Midwest measure with measures for the other regions.
We'll press Control-C to copy the chart and paste with Control-V.
We'll use this new chart to plot revenue for the South region.
To do this, we need to create a new measure, calculating the proportion of revenue generated by the South region. We'll modify the formula for the Midwest region measure to accomplish this.
Let's go to Manage Measures.
Edit the Midwest region measure and copy the divide function.
We'll select Cancel, create a new Measure, call it Revenue South Percent, and paste the formula.
We'll then replace Midwest in the numerator with South.
Again, we'll format the measure as a percentage with no decimal places, and select Ok to create the measurement.
We'll then navigate to the new bar chart and replace the Midwest measure with the new South measure.
We'll right-click on one of the bars, add data labels, change the title of the chart to South Revenue, and see that our second bar chart is now complete. We now have charts for the Midwest and South regions.
Off camera, I'll add similar charts for the Northeast and West regions.
Each of these four charts, tracks the percentage of revenue generated in a specific region for the year 2016.
Notice that each chart has a different scale so the data labels are essential for clarifying the actual size of any specific bar.
Although there are some changes in individual months, we can see that there do not appear to be any trends that last for the entire year.
While this chart is clearly useful, it does have some disadvantages.
For example, we cannot see the actual revenue amounts for any particular month for either an individual region or for the company as a whole.
Nevertheless, managers could use this chart to identify important regions for the business.
This concludes our look at DAX.
Through this course, we've demonstrated various common business situations where DAX functions and concepts can be applied.
We've covered such topics as calculating market shares, customizing time offsets, and comparing sales to averages.
While you may encounter other situations beyond the examples we've discussed, the tools learned here should give you a good base for applying DAX to a variety of business scenarios.