Скачать или смотреть How to Calculate Column Percentages in Power BI Using DAX

Learn how to accurately calculate column percentages based on specific values in Power BI using DAX expressions. This guide offers step-by-step instructions for effective data analysis.
---
This video is based on the question https://stackoverflow.com/q/75152270/ asked by the user 'Mike' ( https://stackoverflow.com/u/416442/ ) and on the answer https://stackoverflow.com/a/75189434/ provided by the user 'Mike' ( https://stackoverflow.com/u/416442/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Power BI Calculate the percentage of a column based on one of the values in the column

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Unlocking the Power of Percentages in Power BI

Calculating percentages in Power BI can sometimes feel like a daunting task, especially when you want to relate one value to another within the same column. If you've ever found yourself struggling to create a percentage calculation based on a specific category, you're in the right spot! Today, we're addressing a common challenge faced by Power BI users: how to calculate the percentage of a column based on a specific value in that column.

The Problem at Hand

Let's consider a hypothetical sales scenario using a simple dataset of products. In our table, we have different categories such as Helmets, Gloves, and Pants, along with their respective sales quantities:

CategorySales QtyCurrent Year % Of Sales Compared to HelmetsHelmets150100%Gloves7047%Pants8053%The challenge arises when you want to calculate the percentage of sales for Gloves and Pants relative to Helmets. Initial attempts often yield correct results for the first row but fail for subsequent rows.

The Solution Explained

Fortunately, there's a solution! By utilizing DAX (Data Analysis Expressions) and modifying your existing formula slightly, you can accurately compute these percentages for all categories. The key update is to make use of the ALLSELECTED function in your calculations.

Here’s How to Do It

Define the Variable for Helmets:
First, we'll create a variable that calculates the total sales for the category "Helmets". By using ALLSELECTED, we're able to ensure that our calculation respects the current filtering context and doesn't limit the evaluation just to specific rows.

[[See Video to Reveal this Text or Code Snippet]]

Calculate the Overall Sales:
Next, we need to compute the total sales for the current context, respecting filters for the week and year.

[[See Video to Reveal this Text or Code Snippet]]

Final DAX Measure:
Putting it all together, your final DAX measure will look something like this:

[[See Video to Reveal this Text or Code Snippet]]

Implementing the Solution

Once you've entered this measure in your Power BI model, it should now accurately calculate the percentage of sales for each category based on the total sales from the category "Helmets." You will see:

Helmets will show 100%

Gloves will reflect approximately 47%

Pants will show around 53%

Conclusion

Understanding how to perform calculations relative to specific values in Power BI can greatly enhance your data visualization capabilities. By applying the above method using DAX and the ALLSELECTED function, you can achieve accurate and meaningful sales percentages for your reports. Happy analyzing, and may your Power BI skills continue to grow!