Power BI: Calculate percentage share

Power BI offers extensive options for effectively calculating percentages.
‍

In the world of data analytics and business intelligence, the ability to accurately calculate and display percentages is essential. In this blog post, we dive deep into the art of calculating percentages in Power BI, from the basics to advanced techniques and applications in real-world business scenarios. Whether you're a data analyst looking to expand your skills or a business owner striving for better insights into your data, this guide will help you create the full potential of your data.
‍

Introduction to percentage calculation
‍

Percentage calculations are an indispensable tool in the world of data analysis and reporting. Not only do they serve to present data in a clear and understandable form, but they also allow us to visualize ratios and comparisons in a way that supports immediate insights and decisions. Percentage calculations are particularly valuable in Power BI. They allow us to gain deeper insights into the data by showing relative proportions and changes that are critical to understanding business trends and performance.
‍

With the user-friendly interface of Power BI and DAX functions (Data Analysis Expressions), both simple and complex percentage calculations can be performed. These range from determining the share of a product in total sales to analyzing percentage changes over time. The visualization of this percentage data through different chart types in Power BI supports understanding and decision making. In the next section, we will look at detailed use cases for percentage calculations in Power BI. You can find out more about the basics of calculating percentages in our blog post "Power BI: Calculating percentages".
‍

Detailed examples for calculating percentage shares in Power BI
‍

Calculating percentages is a fundamental but powerful aspect of data analysis. It allows us to understand the size of a subset in relation to the whole and provides valuable insights in a wide range of areas - from financial analysis to market research. In this section, we'll focus on how to calculate percentages in Power BI to effectively analyze and visualize data. We will see how these calculations help us transform complex data sets into clear, meaningful information.
‍

Example 1: Calculation of the percentage share of sales
‍

Situation and objective: Imagine you are a data analyst in a company that sells several products. Your goal is to determine the percentage of sales of a specific product in relation to total sales. This information is crucial to understanding which product contributes the most to the overall business.
‍

Application of DAX functions: In Power BI, we use DAX functions to perform this calculation. Let's say you have a table called Sales with the columns ProductID, ProductName and Sales. To calculate the percentage of sales of a product, you create a new measure with the following formula:
‍

Percentage share of sales =

DIVIDE(

   SUM(Sales[Revenue]),

   CALCULATE(SUM(Sales[Revenue]), ALL(Sales[ProductID]))

)

This formula first adds up the turnover of the selected product and then divides it by the total turnover of all products. If you have formatted the measure as a percentage value, the result is automatically multiplied by 100 and displayed as a percentage value.
‍

Visualization: Once you have created the measure, you can visualize it in a bar chart to compare the percentage of sales of each product. This provides a clear and direct representation of the sales distribution. Note that only if the measure is formatted as a percentage value, the axis in a chart will also have a % sign.
‍

‍

Example 2: Percentage change year on year
‍

Situation and objectives: Another important analysis tool is the calculation of the percentage change in turnover year on year. This analysis helps to identify growth trends or declines and is particularly useful for strategic planning and forecasting.
‍

Application of the DAX functions: Assume that your sales table also contains a Year column. To calculate the percentage change in sales year on year, create a new measure with the following formula:
‍

Percentage change YoY =

VAR SalesThisYear = SUM(Sales[Revenue])

VAR SalesPreviousYear = CALCULATE(SUM(Sales[Revenue]), DATEADD(Date[Date], -1, Year))

RETURN

IF(

   NOT (ISBLANK(TurnoverPreviousYear)),

   DIVIDE(TurnoverThisYear - TurnoverPreviousYear), TurnoverPreviousYear),

   BLANK()

)
‍

This formula calculates the turnover for the current and previous year (based on the filter context) and then determines the percentage change between these two years.
‍

Here is a step-by-step description of the formula:
‍‍

1. definition of the variable
‍

VAR TurnoverThisYear: This line defines a variable named TurnoverThisYearwhich calculates the total turnover for the current year that has been filtered through the filters.
‍

VAR TurnoverPrevious year: This line defines a second variable called TurnoverPrevious yearwhich calculates the total turnover for the year before the current year. Here we use the CALCULATE-function in conjunction with the Dateadd()-function to determine the total turnover for the previous year.
‍

In the DAX formula TurnoverThisYear and TurnoverPrevious year are used as variables to store dynamically calculated values such as total sales for the current and previous year. Variables in DAX serve as caches that allow calculation results to be used efficiently by calculating them once and then using them multiple times in the formula. This not only makes the formula clearer and easier to understand, but also automatically adapts to data changes without having to modify the calculation logic itself. By storing specific calculation results in variables and reusing them, longer formulas in particular become more efficient and their clarity is improved.
‍

2. calculation and return of the percentage change
‍

RETURN: After the definition of the variables, the RETURN-command, which returns the result of the formula.
‍

IF(NOT(ISBLANK(TurnoverPreviousYear)), ... , BLANK()): This condition checks whether TurnoverPrevious year has a value (is not empty). If TurnoverPrevious year has a value, the percentage change is calculated; otherwise the formula returns an empty value (BLANK()). This prevents errors in the calculation if no data is available for the previous year.
‍

(TurnoverThisYear - TurnoverPreviousYear) / TurnoverPreviousYear: This is the actual calculation of the percentage change. It subtracts the previous year's turnover from this year's turnover and divides the result by the previous year's turnover. The subsequent formatting as a percentage value automatically multiplies the value by 100, so that, for example, 0.33 is formatted as 33%.
‍

Visualization: A line chart that shows the percentage change in sales over the years is suitable for visualizing this data. This makes it possible to quickly identify trends and adapt business strategies accordingly.
‍

Application in various business scenarios
‍

The ability to calculate and visualize percentages and changes in Power BI has far-reaching applications in various business sectors. Through two case studies - one in retail and one in financial services - we illustrate how organizations are using these techniques to make data-driven decisions.
‍

Case study 1: Retail - analysis of the percentage of online sales
‍

Situation and challenge
‍
‍Amedium-sized retail company that operates both physical stores and an online store is faced with the challenge of evaluating the effectiveness of its online sales channel. The company wants to understand the percentage share of online sales in total sales in order to analyze the performance of the online store compared to the physical stores and develop appropriate marketing and sales strategies.
‍

Application of Power BI
‍
‍Thecompany uses Power BI to consolidate and analyze its sales data from various channels. A central task is to calculate the percentage share of online sales in total sales. A DAX formula is used for this in Power BI:
‍

Percentage of online sales = DIVIDE(CALCULATE(SUM (sales data [turnover]), sales data [channel] = "Online"), CALCULATE(SUM(sales data [turnover]), All(sales data [channel])), 0)
‍

• CALCULATE(SUM (sales data [turnover]), sales data [channel] = "Online") represents the turnover generated through online sales.
  ‍
• CALCULATE(SUM(Sales data [turnover]), All(Sales data [channel])) is the company's total sales, including all sales channels.
  ‍
• DIVIDE performs a safe division, whereby the third parameter (here 0) specifies the value that is returned if the denominator is zero.
  ‍

In addition, the data is analyzed seasonally to identify trends and patterns in online purchasing behavior.
‍

Results and strategic decisions
‍
‍Theanalysis in Power BI provides the company with important insights:
‍

1. Recognition of the share of online sales: The company can determine exactly what proportion of total sales the online store accounts for.
   ‍
2. Comparison with physical stores: By comparing the online share of sales with that of physical stores, the company can evaluate the performance of its various sales channels.
   ‍
3. Adaptation of strategies: Based on the results, the company can adjust its marketing strategies to strengthen the online sales channel if it is underrepresented, or conversely, increase investment in physical stores if they dominate.
   ‍
4. Recognize seasonal trends: Seasonal analysis helps identify key periods for targeted marketing campaigns to increase online sales.
   ‍

Through the application of Power BI and the targeted use of DAX formulas, the retailer was able to gain valuable insights into the performance of its online sales channel and make informed decisions for a more effective sales and marketing strategy.
‍

Case study 2: Financial services
‍

Situation and challenge
‍
‍Acompany in the financial services sector is faced with the task of analyzing the composition of its investment portfolio. It wants to understand how market changes affect different asset classes and products in order to manage risk and optimize returns.
‍

Application of Power BI
‍
‍Thecompany uses Power BI to carry out in-depth analyses of its portfolio. An important aspect of this is calculating the percentage share of each asset class in the overall portfolio. A DAX formula is used for this:
‍

Percentage share of asset class = DIVIDE( SUM (Portfolio[value]), CALCULATE(SUM(Portfolio[value]), All(Portfolio [asset class])), 0)
‍

In this formula:
‍

• SUM(Portfolio[value]) stands for the value of a specific asset class.
  ‍
• CALCULATE(SUM(Portfolio[value]), All(Portfolio [asset class])) represents the total value of all investments in the portfolio. The All()-function causes the calculation to be carried out on the basis of all characteristics of the asset class column.
  ‍
• DIVIDE performs a safe division, whereby the third parameter (here 0) specifies the value that is returned if the denominator is zero.
  ‍

In addition to calculating the percentage share of each asset class, the company tracks the percentage change in asset values over time. This is complemented by the integration of external market data to assess the performance of the portfolio in the context of market changes.
‍

Results and strategic decisions
‍
‍Theanalysis in Power BI leads to several important insights and decisions:
‍

1. Assessment of portfolio diversification: The company can see exactly how its portfolio is distributed across different asset classes, which is crucial for risk management.
   ‍
2. Recognition of market changes: By tracking the percentage changes in asset values over time and comparing them with market data, the company can better understand how external factors affect its portfolio.
   ‍
3. Strategic reallocation: Based on the knowledge gained, the company makes decisions on the reallocation of investments in order to minimize risk and maximize returns. This can include, for example, shifting funds from more volatile to more stable asset classes.
   ‍
4. Adaptation to market conditions: The company can dynamically adapt its investment strategy to respond to market changes and take advantage of opportunities.

Through the use of Power BI and the targeted application of DAX formulas, the financial services company was able to gain a deep understanding of the composition and performance of its portfolio. These data-driven insights enabled the company to make informed strategic decisions that helped minimize risk and maximize returns.

‍