I consider dynamic metrics as metrics calculated based on the selection of the report user. Picture this: your company has tasked you with creating a report that gives a comprehensive overview of their business performance, but with a catch — you only have three metrics to show. Revenue generated, associated costs in running the business, and profit over time.
But what if you could make the report dynamic, so that the metrics you show could be adjusted according to the user’s preference? That’s where dynamic metrics come into play.
Why dynamic metrics?
Consider the following scenario. Company A, a client of yours, generates thousands of data daily and tasked you to create a report detailing an overview performance of the business. Say you have just three metrics to show in your report.
Revenue generated
Associated costs in running the business
Profit over time
For a report that needs to be kept updated, performance is something to always look out for while developing the report.
One of the many things that affect a slow report is the number of charts. Note that it is not the only thing that slows down a report. Other things could be done to improve performance. But in this scenario, the report will have to connect to a large volume of data, and I want to ultimately minimize the number of seconds charts will add to the report performance without having to dial down on the information reported. That’s where dynamic metrics come in because the more the charts, the more seconds are added to the report performance.
To follow through on creating dynamic metrics, download the data used here. Below is the data model for the report.
The metrics table contains the metrics we would like to show dynamically, while the Key and Supporting Measures are tables to hold our DAX measures.
You can either create the metrics table via the Enter Data feature or import the table from an excel sheet.
I prefer the enter data feature. Below is the metric table used. The index is for sorting on the report page.
Then, we must create a measure that refers to any item list selected on the metrics table. This is done using the SELECTEDVALUE() function.
Now, we create individual measures that calculate the metrics.
Total Revenue
2. Total Cost
3. Profit
The recipe is almost done.
Photo by Eden Constantino on Unsplash
All left is to incorporate the three measures into a single DAX measure, as seen below.
The switch function is a nested if-statement but cleaner and easier to read. Recall [Metric] refers to the metric selected on the metrics table. The DAX above says that if Revenue is selected, use the revenue measure. If cost is selected, use the cost measure. If profit is selected, use the profit measure; if nothing is selected, use the total revenue measure.
Pretty straight-forward right
How, then, do we use this measure in the report?
Let’s go into PowerBI to see this.
The beauty of this is that it works similarly to how all DAX works. For instance, you can still make a month-on-month comparison, year-on-year comparison etc.
Last year’s value for the selected metric
2. Periodic comparisons
Note that the month-on-month growth here is this month (Nov 2022) vs the same month last year (Nov 2021) and not this month (Nov 2022) vs the previous month (Oct 2022). The month column on the calendar table will provide context to the measure.
3. Daily average run rate
You can see all three measures are based on the selected metric, meaning the calculations will be done based on the chosen metric.
You can view the completed report here.
That would be all. Adios.
コメント