cancel
Showing results for
Did you mean:
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
Solution Sage

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

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.

Solution Sage

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

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.

Solution Sage

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

Helper II

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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors