Understanding the Need for Dual Metrics

In deep data analysis, presenting a single metric often fails to tell the complete data story. For instance, knowing a store generated $50,000 in sales is useful (the absolute value), but knowing that $50,000 represents 45% of the total company sales (the relative percentage) provides critical context regarding that store’s performance dominance or contribution. Displaying both metrics together in a single, cohesive visual element—a challenge in standard visualization tools—is paramount for insightful reporting.

Achieving this dual display using a standard bar chart requires intricate layering or custom visuals, which can sometimes compromise report performance or maintainability. Fortunately, Power BI provides a flexible solution by repurposing one of its simplest components: the Table visual. This approach is highly recommended for its stability and straightforward implementation, offering a clean display that integrates the graphical representation (the bar) with the numeric details (value and percentage).

The goal is to create a dynamic report element where the length of the bar correlates directly with the magnitude of the absolute value, while numerical columns display the precise monetary amount and the calculated percentage relative to the grand total. The technique demonstrated here is the most efficient and standard way within the Power BI environment to produce this highly requested format, bridging the gap between graphical impact and numerical precision.

Why Use the Table Visual for Bar Charts?

While the dedicated Column and Bar chart visuals are excellent for basic presentations, they limit the flexible placement of multiple data labels derived from different calculation contexts (i.e., absolute sum vs. percentage of total). The Table visual, fundamentally designed to display detailed row-level information, excels because it allows the inclusion of multiple instances of the same measure, each formatted differently.

The key to this method lies in applying Conditional Formatting specifically, the Data bars feature, to one of the measure columns within the table. This conditional formatting effectively renders a horizontal bar within the cell based on the value, transforming the column into a visual bar chart component. Because the table structure allows us to add the ‘Sales’ measure multiple times, we can dedicate one column entirely to the graphical bar display and a second column entirely to the percentage calculation.

This particular technique is often preferred by advanced Power BI developers because it provides superior control over alignment, spacing, and font characteristics compared to trying to force multiple data labels onto a standard visual. Furthermore, the resulting visual is responsive and scales well across different report layouts, ensuring clarity regardless of the viewing device.

Initial Data Preparation and Setup

To illustrate this process, we will utilize a simple sales dataset containing store names and their corresponding sales totals. Ensure your data model is correctly loaded into Power BI Desktop before proceeding with the visual creation steps. The dataset used in our example includes the fields Store and Sales.

Our objective is to visualize the sales performance of each store, showing the raw dollar amount in conjunction with the calculated percentage of total sales. This requires switching to the Report View, which is the canvas where all visualization building takes place. The Report View is accessed via the dedicated icon on the left navigation pane of the Power BI Desktop application.

Once in the Report View, the first step is to locate and select the appropriate visual type from the Visualizations pane. Since we are employing the clever workaround, we will select the Table visual icon, positioning it onto the report canvas. This initial setup establishes the foundation for the data display we intend to build.

Step 1: Inserting the Table Visual and Adding Fields

After selecting the Table visual, the next critical step is populating it with the required data fields. We need to drag the dimensional field (Store) and the quantitative field (Sales) into the Columns panel of the visual. Crucially, to accommodate both the graphical bar and the percentage calculation, the Sales field must be added twice to the Columns panel.

This duplication is intentional and allows us to apply different formatting rules to each instance of the ‘Sales’ measure. The first instance of ‘Sum of Sales’ will be designated for the graphical representation using Data Bars, while the second instance will be converted to display the percentage value. Both instances, by default, will initially show the raw sum of sales.

The setup in the Visualizations pane should look like the image below, confirming that Store is listed once and Sales (defaulting to ‘Sum of Sales’) is listed twice in the Columns well. It is important to verify that Power BI has correctly aggregated the sales data, which typically happens automatically when dragging a numeric field into a table context.

Step 2: Applying Conditional Formatting with Data Bars

Now, we transform the first numeric column into the visual bar chart element using Conditional Formatting. Right-click on the first instance of the Sum of Sales field within the Columns panel. This action reveals a context menu with various options for modification and formatting.

From the context menu, hover over Conditional formatting. A secondary menu will appear, offering several formatting options such as Background color, Font color, Icons, and, most importantly for this task, Data bars. Clicking on Data bars opens a configuration window where you can customize the appearance, including bar color, direction, and whether to show the bar only or the bar and the value.

For a clean visual result that minimizes redundancy, you might choose to show the bar only (by checking the ‘Show bar only’ option in the Data bars settings), as the actual value will be displayed in the adjacent column. However, even without modifying the default settings in the pop-up, simply activating the data bars will immediately render the horizontal bar visuals within the table cells, proportional to the sales amount for each store.

Step 3: Calculating and Displaying Percent of Grand Total

The next step focuses on the second instance of the Sum of Sales field, which will be used to display the relative proportion of sales. Right-click on the second Sum of Sales field in the Columns panel. This time, instead of conditional formatting, we will change how this field calculates and displays its value.

Hover over the Show value as option in the context menu. This is where Power BI offers standard quick calculations, removing the need to write complex DAX for common measures. Select Percent of grand total from the list. This transformation instantly recalculates the values in that column, displaying them as a percentage relative to the total sales across all stores.

This crucial step completes the functional requirement of the visualization: we now have the store name, the visual bar representation of the sales value, and the corresponding percentage of the grand total, all side-by-side within the same visual. At this point, the Table visual should clearly show three distinct columns reflecting the required metrics.

Upon completion of this step, the interim visualization will clearly show the store name, a narrow column containing the visual Data Bars, and the final column detailing the percentage. While functionally correct, the visual appearance may still need refinement to maximize readability and impact, particularly concerning the width of the data bars.

Refining the Visual: Adjusting Column Widths and Interpretation

The final, but often critical, phase involves optimizing the visual aesthetics, specifically addressing the column width dedicated to the Data Bars. By default, Power BI tables attempt to auto-size columns, which can result in overly narrow bars that are difficult to interpret quickly. Increasing the column width enhances the visual effect and makes the relative lengths of the bars more immediately discernible.

To adjust the width, navigate to the Format your visual tab (the paint roller or paintbrush icon) in the Visualizations pane. Expand the Column headers section, then look for Options. Within Options, you must disable Auto-size width. Disabling this feature grants manual control over column dimensions, preventing Power BI from automatically compressing the columns.

Once auto-sizing is disabled, you can manually resize the column containing the data bars directly on the report canvas. Hover the mouse over the right edge of the column header (the first Sum of Sales header) until the resizing cursor appears, and then drag the boundary to the right. This action will increase the cell width, extending the horizontal Data Bars and improving the overall visual impact of the pseudo-bar chart.

The successfully adjusted table now serves as a highly effective visualization, displaying the store name, the sales magnitude represented graphically by the bar, and the precise percentage contribution displayed numerically. This composite visual element is powerful for summarizing key performance indicators and providing necessary context without cluttering the report with separate charts or excessive data labels.

We have now successfully created a visualization that leverages the Table visual and Conditional Formatting to display both the value and percentage for each bar representation, offering a comprehensive overview of the data distribution.

Conclusion: Enhancing Data Storytelling

The ability to present dual metrics—absolute value and percentage contribution—within a single visual element is a hallmark of sophisticated data reporting. By utilizing the flexibility of the Table visual and the powerful functionality of Conditional Formatting in Power BI, report designers can craft visualizations that are not only highly accurate but also immediately actionable. This technique transcends the limitations of standard charts by integrating tabular detail with graphical summary.

Remember that effective data visualization hinges on clarity and context. When data consumers see the bar (magnitude) next to the percentage (relative importance), their ability to draw informed conclusions accelerates dramatically. This method reduces cognitive load and ensures that key insights—such as identifying the highest contributing stores or regions—are instantly recognizable, regardless of whether the raw numbers are large or small.

For those seeking to explore further techniques in data visualization, especially involving dynamic calculations and complex formatting within the Microsoft ecosystem, mastering conditional formatting is essential. This example merely scratches the surface of what can be achieved when repurposing visual elements in Power BI to meet specific reporting demands.

The following tutorials explain how to perform other common tasks in Power BI: