Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
common763
Helper III
Helper III

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

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:

barritown_0-1686127478462.png

 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

View solution in original post

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

View solution in original post

5 REPLIES 5
common763
Helper III
Helper III

Excellent Job.  Works perfectly.  Thanks so much. 

barritown
Super User
Super User

Hi @common763,

 

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

 

barritown_0-1683722265136.png

 

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

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. 

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:

barritown_0-1686127478462.png

 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.