Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# DAX Calculation Groups To Avoid Unpivoting Columns

by | 7:00 pm EDT | April 05, 2021 | Power BI

In this tutorial, I’ll demonstrate how you can create 2 split percentages in a donut chart using DAX calculation groups while avoiding having to unpivot data in Power Query. You can watch the full video of this tutorial at the bottom of this blog.

This actually came from a question raised by one of our members from the Enterprise DNA forum. The member was trying to achieve a donut chart visualization where he wanted to split plant and mining costs as shown below.

We can easily achieve this by creating a measure for plant costs and another for mining costs. The measures will have the SUM function, which is literally just adding up the plant costs for the Plant Cost measure and the mining costs for the Mining Cost measure. Then, we can bring these measures into the values section of the visualization.

We can also have these measures in a slicer by creating a calculated column or a custom column in Power Query.

With this, we can have different category columns that we can use in a donut chart and on a slicer.

However, in this particular case, the member was not looking to unpivot the data because of a substantial back-end of data. So he was looking for some alternative solution to get a slicer on the page along with the donut chart. To do this without unpivoting the data, we are going to utilize DAX calculation groups.

## Creating DAX Calculation Groups

To create calculation groups, we go to External Tools, then click on Tabular Editor.

Inside the Tabular Editor, we right-click on Tables, then select Create New, Calculation Groups.

We can then name our calculation group. In this case, we’ve called it Calculation Group. We need to create two calculation items within this. And so, we right-click on Calculation Group then click Create New, Calculation Item.

We’ll call the first one as Plant Cost. And then, we’ll bring in our Plant Cost measure into the Expression Editor.

We’ll call the second calculation item Mining Cost. Then we’ll bring in the Mining Cost measure as well. After that’s done, we click save and refresh our Power BI desktop.

## DAX Calculation Groups In A Donut Chart And Slicer

We can now create our donut chart. We bring in our Overall Cost into the Values section and then our calculation groups into the Legend section. Now we can see that the plant cost is 66.9% of the overall cost and the mining cost is 33.1%.

We can also have our calculation groups in a slicer on the same report page. This is useful when you have multiple visualizations within your report and you want to dive into particular data to get specific results.

## Conclusion

Using calculation groups is a simple and easy technique that you can use if you want to avoid unpivoting data. You can quickly create visualizations and slicers using calculation groups.

I hope you’ve found this tutorial helpful and that you’re able to apply it within your own reports. Check out the links below for more related content.

Cheers!

Mudassir

## Python AttributeError: ‘dict’ object has no attribute Fix

Python is widely appreciated for its readability and ease of understanding. Among its numerous...

## Power BI vs Tableau: Differences Compared 2023

In the world of data visualization and analytics, two industry-leading data visualization tools stand...

## Pandas Drop Index Column: Explained With Examples

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality...

## Microsoft DP-500 Explained (Bonus Cheat Sheet)

Data is the backbone of businesses these days, and having proof that you know how to handle and make...

## Power BI Financial Dashboard Examples: Let’s Get Specific

Power BI financial dashboards provide a quick and easy way to monitor an organization's financial...

## Power BI Themes: User Guide With Examples

Power BI is a powerful business analytics tool that helps you visualize and analyze data from various...

## Power BI Hierarchy: User Guide With Examples

Imagine being able to look at your data from every which way — from the bird's eye view right down to...

## Power BI Calendar Table: What Is it And How to Use It

Time is of the essence in the world of data analysis, and without a proper understanding of when or how...

## How to Sort Alphabetically in Excel with Multiple Columns

Ever find yourself staring at a heap of info in Excel, scratching your head about how to make it all...

## 4 Ways How to Share a Power BI Report: It’s Easy

Ever found yourself sitting on a treasure trove of insights, all neatly packed in a Power BI report,...

## Top 21 Power BI Report Examples: Inspiration to Visualize Your Data

If you're looking for a powerful tool to help you visualize and analyze data, then Power BI reports...

## How to Use ChatGPT To Write SQL Queries

Writing SQL queries can be time-consuming and challenging. If you are tasked with creating solutions...