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

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...