DAX - Percent of Total vs Percent of Grand Total

In this video, Mitchell Pearson from Pragmatic Works takes a deep dive into calculating percentages in Power BI using DAX. He discusses the concept of calculating the percentage of total sales and compares it to the percentage of grand total, providing practical insights into when and why you might choose one method over the other.

Key Takeaways:

• Learn how to calculate a percentage of total sales using DAX.
• Understand the difference between calculating a percentage of total and a percentage of grand total in Power BI.
• See how to use Power BI’s built-in features to quickly calculate percentages without DAX.
• Discover how DAX offers greater flexibility and control over calculations, especially when working with filters and custom contexts.

Building a Percentage of Total Using DAX:

Mitchell Pearson explains that when building a percentage of total, he typically uses DAX. This method is taught in his training boot camps and used during client interactions. The goal is to calculate the percentage of sales for a specific country (e.g., the United States) in comparison to the total sales across all countries.

To create the calculation, Mitchell first sets up a variable to capture the total sales from all countries. Then, he divides the sales of the United States by the total sales to find the percentage. He formats the result as a percentage, which in this example, is 31.98%. This DAX approach gives a detailed, customized result, and Mitchell emphasizes how this method allows you to add more flexibility and control over the calculation, especially when filtering or adjusting the context.

Using Power BI Without DAX:

Next, Mitchell demonstrates how to achieve the same result in Power BI without writing DAX. Instead of manually calculating the percentage, he shows how to use Power BI's built-in options to display the sales as a percentage of the grand total. By simply adding the total sales to a table and using the drop-down menu, you can display the percentage without writing any code.

This method is quick and easy but doesn’t offer the same level of control as DAX. While Power BI's built-in feature works well for basic calculations, DAX is necessary when more complex filtering or context adjustments are needed.

Key Differences Between the Two Methods:

• Built-in Power BI Method: Quick, easy, and no DAX required, but lacks flexibility when dealing with additional filters or custom contexts.
• DAX Method: Offers greater flexibility and control, especially when working with multiple filters and complex calculations.

Conclusion:

Mitchell concludes by reiterating that both methods are valid. The choice between using DAX and Power BI’s built-in feature depends on your needs. If you require more control over filtering and context, DAX is the better option. However, for quick calculations, Power BI’s built-in functionality is perfectly sufficient.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Dax and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.