How to Show Percentage Values in a Stacked Column Chart in Power BI

We will walk through the process of data preparation, DAX formula creation, and chart configuration to ensure precise proportional display of your data within Power BI.

Setting Up Power BI: Loading the Sample Dataset

The foundation of any successful visualization in Power BI is properly loaded and modeled data. For this tutorial, we will utilize a simple dataset containing information relating to individuals’ demographics and their favorite sports. This dataset is ideal for demonstrating how to calculate and display the percentage contribution of one category (Gender) within another (Sport).

Our sample dataset includes records for 20 individuals, providing two critical columns: Gender and Sport. Ensure that this data is successfully loaded into your Power BI environment under a designated table name, which we assume here to be ‘my_data’. The structure of the imported table should resemble the following:

Once the data is loaded and available in the data pane, we can proceed to the crucial step of defining the calculation logic necessary to derive the required percentages for our visual.

Defining the Calculation: Creating a DAX Measure for Percentages

To accurately display the percentage of each gender within the total count for each sport, we must create a custom Measure using Data Analysis Expressions (DAX). A measure is a dynamic calculation that processes data based on the current filter context applied by the report visualization, such as the specific segments shown in a chart column.

Navigate to the Table tools tab located in the top ribbon of the Power BI interface. Within this section, locate and click the New measure icon. This action opens the formula bar where we will input the required DAX logic. The goal of this measure is to calculate the count of individuals filtered by gender, divided by the total count of individuals filtered only by sport (i.e., ignoring the gender filter context).

Enter the following formula into the DAX formula bar. This calculation, named Percent of Total, employs the functions DIVIDE, COUNT, CALCULATE, and ALLEXCEPT to achieve the desired row-level percentage calculation within the context of the visual’s columns:

Percent of Total = 
DIVIDE (
    COUNT('my_data'[Sport]),
    CALCULATE (COUNT('my_data'[Gender]), ALLEXCEPT('my_data', 'my_data'[Sport]))
)

The ALLEXCEPT function is critical here. It ensures that the denominator of the division removes all filter contexts from the ‘my_data’ table, except for any filters applied specifically to the ‘Sport’ column. This guarantees that for a given sport (e.g., Basketball), the denominator counts the total number of individuals, providing the correct base for percentage calculation, regardless of the gender segment currently being drawn on the chart.

Formatting the Measure for Percentage Display

After successfully defining the Percent of Total Measure, the resulting output will be a decimal number (e.g., 0.7143). To make this result understandable to report consumers, we must apply the appropriate percentage formatting within Power BI.

With the new measure still selected in the data pane, navigate to the Measure tools context tab in the ribbon. Locate the Format dropdown menu. Click the dropdown arrow next to Format and choose Percentage from the available options. This conversion automatically handles the multiplication by 100 and adds the percentage symbol to the measure’s output.

You should also verify the number of decimal places displayed for the percentage. While typically two decimal places offer adequate precision, you can adjust this setting next to the format selection. Properly formatting the measure at this stage is essential because it ensures that when we subsequently add this measure to the visual’s Tooltips, the percentage is displayed correctly and consistently across the entire report.

Visualizing Data: Inserting the Stacked Column Chart

With the custom percentage measure successfully created and formatted, the next stage involves inserting and preparing the visualization. Return to the Report View by clicking the icon on the left-hand navigation pane of the Power BI Desktop application. This is your canvas for designing the interactive report.

From the Visualization pane, select the Stacked Column Chart icon. This specific chart type is ideal because it clearly separates segments (Gender) within a total category (Sport) while still allowing the total height of the column to represent the absolute count. This dual representation capability is crucial for our goal.

An empty representation of the stacked column chart will now appear on your report canvas, waiting for data fields to be assigned. The successful display of percentage values relies heavily on the correct mapping of fields to the chart’s properties.

Mapping Variables: Defining Axes, Legends, and Tooltips

We now assign the necessary data variables to the chart’s structural elements to build the visualization and integrate the custom percentage measure. Follow these precise steps:

1. Drag the Sport variable from your data fields list and place it under the X-axis field well. This organizes the columns horizontally by sport category.
2. Drag the Gender variable and place it under the Y-axis field well. Power BI automatically applies a count aggregation, determining the height of the column based on the total number of individuals per sport.
3. Drag the Gender variable a second time and place it under the Legend field well. This action segments the columns, using color to distinguish between the counts of Male and Female within each sport.
4. Drag the crucial Percent of Total Measure and place it under the Tooltips field well. This ensures the percentage calculation is available upon interaction, without modifying the main visual elements.

Completing these steps produces a standard stacked column chart where the X-axis shows individual sports, the Y-axis shows the total count, and the colors represent gender segments.

The visual now effectively displays counts, and the configured Percent of Total measure is ready to provide proportional context through interactive tooltips, achieving the primary goal of this exercise.

Interpreting the Results: Understanding the Percentage Tooltip

The advantage of using the tooltip approach is that it maintains a clean visualization while providing deep, contextual data upon user interaction. When you hover your mouse cursor over any individual bar segment in the chart, a tooltip will instantly appear, providing a summary of both the absolute count and the calculated percentage that the segment represents of the total column.

The tooltip includes the standard fields (Sport, Gender, and Count of Gender) along with the result of our custom measure, Percent of Total. This integration allows for comprehensive understanding in a single interactive element.

Let’s examine the data presented for the ‘Basketball’ category. The entire column indicates that there were 7 total individuals who preferred basketball as their favorite sport. When we hover over the dark blue segment, which represents Males:

• The tooltip shows that 5 of these individuals were Males (the absolute count).
• The Percent of Total measure shows 71.43%.

This proportional value is the result of our DAX calculation (5 Males / 7 Total Enthusiasts = 0.7143). This confirms that males represent 71.43% of the total demographic for basketball in this sample. This technique significantly enhances the analytical value of the stacked column chart by providing proportional context for every segment instantly.

Conclusion and Next Steps

By leveraging custom DAX measures and strategically placing them within the Tooltips property of the visual, we have successfully integrated proportional data into a stacked column chart in Power BI. This approach ensures accuracy, maintains report clarity, and significantly improves the interpretability of your visualizations by providing both absolute counts and calculated percentages.

Mastering the use of the CALCULATE and ALLEXCEPT functions in DAX for manipulating filter contexts is key to unlocking advanced, dynamic reporting features in Power BI. This specific technique for percentage display is a fundamental skill applicable across diverse business intelligence requirements, from financial analysis to operational performance dashboards.

For further learning on advanced Power BI concepts, consider exploring these related tutorials:

• How to calculate running totals using time intelligence functions in Power BI.
• Techniques for implementing dynamic filtering using slicers and bookmarks.
• Creating advanced conditional formatting rules to highlight anomalies in reports.