Complete Guide to Power BI Dynamic Formatting (Part 2) – How to Automatically Change Units Based on KPIs (ISINSCOPE + Dynamic Format String)

In the previous post, we covered the basic concepts of Dynamic Format Strings in Power BI. In this installment, we take it a step further by introducing core patterns used directly in the field:

• • “Reports where units change automatically based on the indicator (KPI)”
• • “Advanced design that expresses both % and currency within a single column”

In practice, I have seen many cases where reports become unnecessarily complex due to these requirements. Especially as the number of KPIs increases, “unit management” becomes increasingly tedious. This article summarizes how to solve this problem cleanly.

• ✔ When a KPI is selected → Automatic unit change
• ✔ In tables → Rows show %, Totals show currency
• ✔ With 1 column → Simultaneous expression of Weight + Total

These are patterns you can apply immediately in your work.

1. Chart Unit Switching: Utilizing Field Parameters

This is a structure where the axis immediately changes to $ when a user selects 'Sales' in a slicer and to % when they select 'YoY%'.

Implementation Process

1. Create Base Measures: First, create the metrics necessary for analysis, such as [Total Sales] and [YoY Growth %].
2. Create Field Parameters: Go to the [Modeling] tab > [New Parameter] > [Fields] and select the above measures to create a 'KPI Selector' table that groups them.
3. Link Dynamic Formatting: If formatting is specified for each source measure included in the field parameter, the chart will automatically change the axis units according to the slicer selection.

Expert Tip: Using Field Parameters allows you to navigate multiple indicators within a single chart area, innovatively saving dashboard space. For detailed setup methods, please refer to the [Igloo BI: Field Parameters Basic Guide].

Curious about how to set up Field Parameters?

If you are not yet familiar with creating field parameters, I recommend checking out [Igloo BI’s Complete Guide to Field Parameters (Link)] first.
[Link to Post No. 66: To be updated later]

Navigating multiple indicators within a single chart area allows for innovative saving of dashboard space.

2. Table Switching: Rows show Weight, Total shows Grand Total

We want to see these simultaneously in a single column:

• Contribution by Brand (%)
• Overall Sales Volume (Currency)

Usually, 2 columns are required, but we will solve this with 1 column.

Analysis Scenario:

• Individual Brand Rows: Display the contribution weight (Contribution %) of that brand within the total variance.
• Total Row: Display the grand total for the period.
• Through this method: Users can identify at a glance "which brand contributed the most (%)" out of the grand total.

1) Weight/Total Switching Logic using ISINSCOPE

This is a DAX pattern designed to calculate the sales weight (Weight %) of a specific brand relative to the selected total sales at the individual brand row level, while returning the grand total sales amount at the total row level.

Step 1. Data Example

Month	Brand	Sales	LY Sales
2024-01-01	Nike	45,000	42,000
2024-01-01	Adidas	35,000	32,000
2024-01-01	New Balance	25,000	24,000
...	...	...	...
2024-12-01	Nike	140,000	125,000

Step 2. Core Concept: ISINSCOPE

ISINSCOPE('Salesdata'[Brand]) ⇒ Determines whether the current evaluation context is at the granular level (Brand) or the aggregate level (Total).

Context	Result
Brand (Row Level)	TRUE
Total (Grand Total)	FALSE

Step 3. Measure (Weight + Total Switching)

✔ Filter Function Selection Guide: ALLSELECTED vs REMOVEFILTERS

Many people wonder which function to use when calculating "weight relative to total." The correct answer depends on the intended purpose of the report.

① ALLSELECTED('SalesData'[Brand])

• Purpose: Use this when you want to see 100% within the range selected by the user.
• Feature: If the user picks only 'Nike' and 'Adidas' via a slicer, it recalculates the weights based on those two.

② REMOVEFILTERS('SalesData'[Brand])

• Purpose: Use this when you want to fix the weight based on the 'entire dataset' regardless of slicer manipulation.

Igloo BI’s Recommended Pattern

"If you want to show a fluid 100% that reflects the influence of the slicer according to the report's design intent, maintain ALLSELECTED."

2) Dynamic Format String: Automatic M, K Unit Transition

Now, we make it so a single value becomes either % or currency.

Setup Method:

1. Click the Dynamic_Sales_Weight measure in the [Data] or [Model] view.
2. Activate Dynamic Formatting: In the top [Measure Tools] tab > [Format] dropdown menu, select [Dynamic]. 
   
   
   
3. When the dropdown changes to [Format], enter the logic. 
   
   
   

✔ Why use SELECTEDMEASURE()?

Using SELECTEDMEASURE() is strongly recommended for:

• Calculation Group Scalability
• Ease of Maintenance

[Dynamic Formatting Logic omitted as per instructions]

3. Expected Effects in Practical Application

Applying this design dramatically increases the space efficiency and readability of your reports.

• Share Analysis: Users immediately grasp the weight each brand occupies in total sales without separate calculations.
• Layout Maintenance: Cleanly control total amounts by displaying them as 105.0K to prevent stretching table columns.
• Decision-Making Speed: Showing 'Relative Weight' and 'Absolute Magnitude' simultaneously prevents interruptions in the analysis flow.

Wrapping up: Why Should You Design This Way?

Beyond simply listing data, changing the character and unit of numbers according to the Context the user wants to see is the very essence of Business Intelligence. We hope you build smarter Power BI reports through the ISINSCOPE and Dynamic Formatting strategy proposed by Igloo BI. For additional DAX optimization techniques, please refer to other guides on our blog!

<Other posts on the blog>

• Standard Deviation: The Complete Guide to the Core of Business Data Analysis

• Power BI DAX Integrated Analysis to Fix the Average Trap (Part 1-2): Median-driven Segmentation Strategy using Power BI's Box and Whisker Chart

• Mastering BLANK in Power BI - Part 1: BLANK vs. 0 vs. NULL An Integrated Perspective across SQL, Power Query, and DAX

• Power BI Chart Tips : Advanced DAX - Pin "Other" to the Bottom : The Subtle Detail That Determines Readability, Custom Sort Order