Introduction
The April 2023 release of Power BI Desktop gave us dynamic format strings for measures. While the end result could be previously accomplished by using calculation groups, we can now have measures with different formats based upon various conditions – no need to go into Tabular Editor to do this now!
While we can use parameters to switch measures, things get more complicated if you need to include more selections such as a Top N filter in the measure for all visuals across a page. You may still need to write your own measure selection logic in cases such as this.
We also recently got the ability to add subtitles underneath the titles of visuals.
This week, we are making a report that allows users to choose both a metric and a number of products to visualize by various dimensions. The visuals must show the measure with appropriate formatting (e.g., whole numbers, percentage with two decimal points, currency).
Requirements
- Add a text box to dynamically populate a page title. The title should include the selected metric and the number of top products selected.
- Allow the user to choose the number of top products to be included in the report (1 – 5).
- Allow the user to choose the metric to be used in the report visuals. The options should include: # of Customers, Gross Margin, Gross Margin % ([GM%] in the model), Total COGS, and Total Revenue.
- Add a table that shows the selected metric by product for the top N products
- Add a line chart that shows the selected metric for the top N products by month
- Add a bar chart that shows the selected metric for the top N products by industry
- Add a map (your choice of map type) that shows the selected metric for the top N products by state
- Populate the title of all 4 main visuals by using DAX that contains the number of products and metric selected
- Populate the subtitle for the 4 main visuals
- Ensure that the measure used in the 4 main visuals formats the results appropriately (without creating a calculation group, using the new dynamic measure format strings):
- whole number for [# of Customers]
- whole number with thousands separator for Gross Margin, Total COGS, Total Revenue
- Percentage with 2 digits after the decimal for Gross Margin %
Important Note: You need the April 2023 (version 2.116.622.0) or later version of Power BI Desktop to complete this challenge. As of the date this challenge was published, there is a preview feature labeled “Dynamic format strings for measures” that must be enabled in the Options menu.
Dataset
This week’s challenge uses the Customer Profitability Sample provided by the Power BI team. The model is already made (no Power Query requirements), but you’ll need to do a bit of DAX work to complete the challenge.
Direct download link: https://download.microsoft.com/download/6/A/9/6A93FD6E-CBA5-40BD-B42E-4DCAE8CDD059/Customer%20Profitability%20Sample%20PBIX.pbix
Share
After you finish your workout, share on Twitter using the hashtags #WOW2023 and #PowerBI, and tag @MMarie, @shan_gsd, and @KerryKolosko. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.
Pingback: How to use the new dynamic format strings for measures in Power BI – SQLServerCentral