DAX and Measures
1. DAX and Measures
Hello! Let's have a look at how we can actually use DAX to create measures. Just as in the previous demo I'm working again with the Contoso dataset. Before we dive into it, we refresh our knowledge of implicit vs explicit measures. Let's create a card where we would like to get a count of the number of rows. If we add amount, it will sum it naturally, but we can change the aggregation to count. This is an implicit measure. Before we do the same using an explicit measure, we create an empty table in the data pane where we can store our newly created measures. Let's name the table _Measures. That way we can easily find our measures we are creating without having to look through all tables. Note I added an underscore before measures, so it would appear on top, above all the other tables. We can rightclick on the table and select new measure. Let's name it Transaction Count, and use the COUNTROWS function. It counts all the rows in a table, so it should give us the same result. This DAX expression takes one argument, which is a specific table. Let's fill in the name of the fact table to count the rows inside. We create another card to verify this. The result is again 3708. Great - both numbers match! Secondly, we want to calculate the average amount for the actual Scenario. Remember there are three scenario’s: Actual, Budget and Forecast. In this case we are only interested in the actual scenario. We can combine the average with a calculate function to do this. Once again we create a new measure. Let's name it actual average transaction value. We can press shift enter to start typing on a new line, so the formula is easier to understand. Let's start by investigating the CALCULATE function. This formula evaluates an expression in a context modified by filters. We can use the average function as the first argument, and a filter as the second argument. We take the average of the amount, and filter scenario name from the fact table to be equal to actual. The amount is a currency value, so let's change the format to dollar. We navigate to the report pane so we can add it to a card. The average transaction value = 14.7M. From looking at the table we know that the transaction values range from around 20K, to over 140 million dollars. It might be worth it to investigate this crazy spread of sales amounts, but that's outside the scope of this demo. The last topic we want to get into is quick measures. We can right-click the measures table and click quick measure. This is a great way to create more complex functions or discover how certain functions work. We have a dropdown of different calculations, and let's go for quarter over quarter change here. We add amount as the base value, and date from the calendar table as the date. We'll leave the number of periods as 1 for now. If we would change this value to 2, it would not calculate the growth compared to last quarter, but to two quarters ago. The formula Power BI created looks rather complex, and is worth a quick investigation. The first part is a filter on date, so let's ignore that part for now. The second part uses a variable function as we saw in the previous video. Using a VAR statement, It defines the amount of previous quarter using a CALCULATE function. The RETURN statement informs us the variable has been defined. Finally it subtracts the amount of the previous quarter from this quarter, and divides it by the amount of last quarter. This is the formula to calculate quarter over quarter growth, so that makes sense. That's it for this demo. It's your turn to use these functions to discover worldwide insights about WorldWide Importers! Enjoy!2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.