1. Iterating functions in Power BI
In this final video of the chapter, I’ll first show you a quick tip to tidy up and organize your created measures. When you have a large number of measures, it makes sense to group them into subfolders to keep a nice overview. You can do this in the Model view. Select a measure, and choose a Display folder name. After pressing Enter, the measure will move to this newly created folder. You can drag other measures directly to this folder, or create new folders as you like.
With that settled, let’s have a look at iterating functions using this report. We have a table with Actual, Budget, and Forecast amounts, split up by year, on top. Underneath, I want to create a table of the sum of Actual amounts only, split up by Product Category Name.
We could drag the Amount column to the product category table and add a filter on that visual to filter out the Budget and Forecast values, but that is not the most efficient way, since that requires all data to be loaded, and then filtered to show only what you want to visualize.
A better alternative is to use the filter context to filter the Actual values using a measure. Let’s create a new measure, and call it Actual Transaction Amount. Use SUMX() to iterate the sum over all rows of the Amount column, filtered by “Actual” as the Scenario Name in the fact table.
Adding this new measure to the table, we can see that the total amount over all years corresponds with the total amount in the upper table. That means that our formula is working, great!
The reason why I added this second table is because I’m now able to rank the Actual Transaction Amount column. You could sort the column by clicking its header,
but with longer tables, it becomes more difficult to see which category is the fifth, ninth, or any other rank. This is where the RANKX() function comes into play. Let’s create a new measure called Actual Transaction Rank, with RANKX() ranking the Dim_ProductCategory table by the Actual Transaction Amount, and add it to the table.
Mm, it seems that all product categories are ranked equally among each other. This can’t be right. What happened is that the RANKX() function ranked the actual amounts using the query context, which is by ProductCategoryName in this table. Since all product categories have only one amount per category, each category will be the only and thus first ranked category. By wrapping the product category dimension table inside an ALL() function, we override the query context and can filter correctly using the filter context, which compares actual amounts over all product categories.
Great! Now we see which categories are in the top 5 of best-selling products. Time to try this yourself!
2. Let's practice!