Analytics: Power BI
Conquering Multi KPI Charts with Dynamic Formatting in Power BI
How to visualize multiple KPIs with different number formats interchangeably using one chart and slicer
Have you ever tried to fit three key KPIs into a single Power BI chart and used a slicer to switch between them?
It can be done by manually creating a table with the KPI names, using that table for a slicer, and then creating a measure that pulls the values from the corresponding KPI measures based on the slicer selection.
But issues arise when these KPIs require different number formats. For instance, if you need to show Revenue (in dollars), Units Sold (as a whole number), and Profit Percentage (as a percentage) in one chart, the chart will only display one number format, leading to inaccurate representation.
So, how do we display KPIs with dynamic number formatting in Power BI?
Let’s dive into the solution:
Requirement:
Let’s say we have a fact table called FACT_Sales that tracks monthly sales data across five columns: "Month," "Revenue," "Cost," and "Units Sold." The goal is to create a single Power BI chart that can display three KPIs—Revenue, Units Sold, and Profit Percentage—interchangeably based on the slicer selection.
Our requirement is to create one chart in Power BI that will present the three KPIs: Revenue, Units Sold and Profit Percentage interchangeably based on slicer selection.
Each of these KPIs requires a different number format:
- Revenue: Comma-separated dollar value with a currency symbol displayed using ‘K,’ ‘M,’ or ‘B’ suffixes as appropriate.
- Units Sold: Comma-separated whole number using ‘K,’ ‘M,’ or ‘B’ suffixes.
- Profit Percentage: Percentage value with two decimal places and a % symbol.
Solution:
Step 1: Create a slicer table with KPI names and their corresponding number formats:
First, we need to create a table to serve as the KPI slicer. Along with the KPI names, we’ll add a column to arrange the KPIs in the desired order within the slicer. Additionally, we’ll include a “Value Format” column that specifies the number format for each KPI.
Step 2: Create a measure with dynamic formatting that toggles between KPIs based on slicer selection:
Assuming we already have the KPI measures that calculates monthly revenue Revenue, Units Sold, and Profit %, our next step is to create a measure that toggles between these measures based on the user’s selection in the KPI slicer.
To do this, we’ll use the SWITCH() and TRUE() functions to generate outputs for various conditions. To capture the slicer selection and apply it to these conditions, we'll use the SELECTEDVALUE() function.
Monthly Sales Chart =
SWITCH(
TRUE(),
SELECTEDVALUE(SLCR_KPISelection[KPI]) = "Revenue", [Monthly Revenue],
SELECTEDVALUE(SLCR_KPISelection[KPI]) = "Units Sold", [Monthly Units Sold],
SELECTEDVALUE(SLCR_KPISelection[KPI]) = "Profit %", [Monthly Profit %]
) Next, from the “Format” drop-down, select “Dynamic”:
This activates a text editing option for the measure, allowing us to use DAX to write conditions that dynamically change the format of the value returned by the measure.
Since we’ve already stored the format strings to be used dynamically based on slicer selection, we’ll once again use the SELECTEDVALUE() function to retrieve the selected value from the "Value Format" column based on the slicer choice.
With these preparations complete, we’re now ready to create our visuals.
Step 3: Apply the relevant columns and measures to the visuals:
Add the KPI column from the KPI table to the slicer and set it as a single-select slicer.
Next, create a column chart by placing the Month column on the X-axis and the Monthly Sales Chart measure on the Y-axis.
With these steps completed, our visuals are ready to go.
Results:
- By selecting “Revenue” in the KPI slicer, the user will see the revenue displayed in dollar value format.
- When “Units Sold” is chosen in the KPI slicer, the chart updates to show the whole number format.
- Selecting “Profit %” in the slicer will change the chart to display the profit percentage with a % sign.
By utilizing dynamic number formatting and slicers in Power BI, you can create a versatile chart that effectively presents multiple KPIs with varying formats. This approach not only enhances data clarity but also allows for a more interactive and insightful analysis experience. With these techniques, you can tailor your visuals to meet specific needs and provide more meaningful insights from your data.
Thank you for reading!
Please feel free to clap 👏 if you found this article helpful and leave a comment or share it with others. Follow me for more insights on analytics. You can find me on LinkedIn.
Don’t forget to subscribe to
and join our Power BI community
