Below is the Pivot Table from the last exercise. Let's create the Measure, Actual, and polish up the Table. Then, let's talk more in-depth about Measures.
Writing the Actual Measure
Start with the workbook created from the last exercise, Measures, or download this file.
With your cursor in the Pivot Table, go to the Power Pivot menu, and select Measures, New Measure.
With your cursor in the Pivot Table, go to the Power Pivot menu, and select Measures, New Measure.
In the Measure dialog box, select the Table, Actual_Data.
Change the Measure name to Actual.
In the formula box, type =SUM(Actual_Data[Sales]).
Check formula. Result should be no errors.
Format by changing the Category to Number, with 0 decimal places.
Ok.
Your Pivot Table now contains the new Measure "Actual". You can compare to the Sum of Sales values you are accustomed to seeing and confirm the Measure resulted in the same output (just formatted).
In the Values area of the PivotTable Fields List, remove both the Sum of Sales metrics. Below is a snapshot of the Pivot Table, which now contains the two Measures, Budget and Actual.
Measures In-depth
With the introduction of Measures, you've unleashed a tremendous amount of calculating power, which hasn't yet been explained. Let's revisit each element of the Measures dialog box to start digging deeper.Table Name
The Table Name selected determines where a Measure is located on the Fields section of the PivotTable Fields List. Note the Actual and Budget Measures in the image below.
Some people recommend placing Measures on the queries where the related data sits, which was the structure selected for this exercise. Selecting the queries to assign a Measure to is a matter of personal preference. The Measure will return the same result regardless of the query assigned.
Measure Name
The Measure Name will be the default displayed name on the Pivot Table, as Budget and Actual are in our exercise. Like a normal Pivot Table, the displayed name can be changed in Value Field Settings. In the example below, the Measure Name would be "Actual" and the displayed name on the Pivot would be "Actual Sales".
Name your Measures something intuitive to make it clear what the Measure is calculating. As your Data Models start to have multiple Measures, such as Actual Sales and Actual Orders, it will be helpful to easily distinguish between the two. In our Data Model, Actual Sales and Budgeted Sales would have been more robust Measure names than Actual and Budget.
💥BEST PRACTICE TIP
Create robust Measure names. Think about possible future uses for your Data Model. Changing a Measure name later can cause headaches. A renamed Measure is removed from any existing Pivot Tables and requires modifying any dependent Measures. 💥
Formula
The formula area is the meat of the Measure and where the DAX language comes in to play. Measures are calculations which display in the Values area of a Pivot Table. As a calculation, Measures always start with an equal sign.
=SUM(Actual_Data[Sales])
The next element is the function. There are more than 200 DAX functions, many which resemble functions in native Excel, but give more Power to your analytics.
In our exercise, we used the function SUM. SUM adds up the amounts in a specified column. All functions are followed by an open and closed parenthesis: =SUM().
For the SUM function, we specified the column as Actual_Data[Sales] surrounded by open and closed parenthesis. Since Data Models can contain multiple tables (queries) which can have the same column names (such as Sales), when specifying a column, include the table name and the column name. Our table name was Actual_Data, and the column we are summing from that table is Sales. Columns are referenced in brackets.
At this point, we've comparatively done a lot of work to mimic the "Sum of Sales" value normally included in a Pivot Table. This is a simple example, designed to mimic something you are likely very familiar with to lay the foundation for more complex Measures and analysis.
As an additional foundational exercise, I recommend reviewing the official documentation for a formula: Microsoft link. As you venture on your own, understanding how non-accountants reference the elements of a function enhances collaboration.
Check formula
After writing the formula, Always press "Check formula". The green checkmark and "No errors in formula" message confirm the formula is written correctly.
A very common error message is the following, unhelpful message.
When the above message is received, check to make sure there is a closed parenthesis at the end of the formula.
Using Tab and the drop down menus can assist with dropping in the function and column names correctly. Unlike Power Query, DAX is not case sensitive.
A very common error message is the following, unhelpful message.
Using Tab and the drop down menus can assist with dropping in the function and column names correctly. Unlike Power Query, DAX is not case sensitive.
Format
When viewing the Measures "Budget" and "Actual" in your Pivot Table, you may have noticed they were formatted with a comma and no decimals.
This was based on the format settings listed in the Measure dialog box. Each time a Measure is used, the format is set and ready to go.
In this lesson, you:
- Practiced writing Measures;
- Compared Measures to the traditional approach of adding Values to a Pivot;
- Explored each component of the Measure dialog box;
- Learned how to write robust Measure names; and,
- Resolved common Measure errors.
There's a lot more to talk about regarding Measures. Subscribe below to be alerted when the next post is ready.
SOLUTION
Prior post: Measures
Next post: Measures inside Measures
💬 Challenge exercise:
Load the Actual_Data query in to PowerBI, and write the Actual Measure, =SUM(Actual_Data[Sales]).
No comments:
Post a Comment