cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

## DAX Formula to group categories in One column

I have an issue and trying to figure it out.  I have two columns that need to somehow be assessed and it isnt working for me.

Column A is an ID column that has 1000's of unique IDS.

Column B has specific categories

Business Rules: When a category is selected, the end-user wants to know what the % of that category is alongside the % of all other categories that shared that ID.

The end-user wants to do some predicitive analysis and asked that we show them what % of each category.  So in the below scenario, if someone selected DEF in a slicer,  a pie chart would show DEF as 50% (2) and EFG as 50% (2).  Basically if DEF is selected, what is the percentage of all other categories in that column sharing the same ID.

Sample Data

ID           CATEGORY

1             ABC

2             DEF

2             EFG

3             DEF

3             EFG

I cant use ID as a slicer because there are 1000's and it just doesnt work.   Was wondering if someone can get me in the right direction for some type of DAX formula or alteration of the data set to get this functioning.

2 ACCEPTED SOLUTIONS
Super User

Hi @common763,

Okay, I've managed to build the pie chart you need with my toy data. Please try this approach with your production dataset.

The idea is:

1) You create a new table with the unique values from the Category column.

``Cats = DISTINCT ( data[Category] )``

It should be standalone, not linked with the main table.

2) You create a measure like this:

``````Filtered Count =
VAR SelectedValues = VALUES ( data[ID] )
VAR ActiveCategory = MIN ( Cats[Category] )
RETURN COUNTX ( FILTER ( ALL ( data ), AND ( data[ID] IN SelectedValues, data[Category] = ActiveCategory ) ), [Category] ) ``````

3) You add a Category slicer on your dashboard based on the column of your main table.

4) You add a Pie Chart visual and use Cats[Category] and the measure from p. 2.

Voila:

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Helper II

Excellent.  I will try this out.  Thanks so much.  Get back when I work it out on my end.

5 REPLIES 5
Helper II

Excellent Job.  Works perfectly.  Thanks so much.

Super User

Hi @common763,

If I have correctly understood your task, you need a measure like this:

In text format for convenience:

``````Measure =
VAR Num = COUNT ( data[ID] )
VAR IDSet = VALUES ( data[ID] )
VAR Denom = COUNTX ( FILTER ( ALL ( data ), data[ID] IN IDSet ), [Category] )
RETURN DIVIDE ( Num, Denom )``````

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Helper II

Ok sorry for the late response.  Our team had a solution but it seems like a band aid.  This kind of gets me there but doesnt properly group based off the selection.  The customer wants to single select a category, so say the select ABC in a slicer.  They then want a pie graph to display the total for ABC and all other categories with the same ID.    So with the below:

1-ABC

1-DDD

1-CCC

1-EEE

2-ABC

2-CCC

2-EEE

Output would equal ABC = 2  DDD = 1 CCC = 2 EEE = 2 or by % of those numbers

We ended up creating two replicated tables, then many to many relationships and it works in BI Desktop, but when I tried to redo it in Report Server I am getting errors and thinking there has to be a better way to handle this.  Any information would be greatly appreciated.  Thanks.

Super User

Hi @common763,

Okay, I've managed to build the pie chart you need with my toy data. Please try this approach with your production dataset.

The idea is:

1) You create a new table with the unique values from the Category column.

``Cats = DISTINCT ( data[Category] )``

It should be standalone, not linked with the main table.

2) You create a measure like this:

``````Filtered Count =
VAR SelectedValues = VALUES ( data[ID] )
VAR ActiveCategory = MIN ( Cats[Category] )
RETURN COUNTX ( FILTER ( ALL ( data ), AND ( data[ID] IN SelectedValues, data[Category] = ActiveCategory ) ), [Category] ) ``````

3) You add a Category slicer on your dashboard based on the column of your main table.

4) You add a Pie Chart visual and use Cats[Category] and the measure from p. 2.

Voila:

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Helper II

Excellent.  I will try this out.  Thanks so much.  Get back when I work it out on my end.

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors