Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Grouping Categories

Hi everyone,   I am designing a pie chart that the categorical field has a lot of values which makes the chart to be clustered. I am thinking creating a new column that will group any category that...
Show and Tell
Tips and Tricks
v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

 

You may create a conditional column in 'Query Editor' or a calculated column in Power BI Desktop.

Conditional column:

 

Calculated column:

Calculated Column Group = 
IF(
    [Value]>=100,
    "Others",
    [Category]
)

 

Result:

 

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I was able to use this to solve it but I ntoiced it does not work well with other filters in the report

Car Group = 
IF (
	CONTAINS ( 
		TOPN (
			10,
			SUMMARIZE ( 
				'Sales',
				[Name],
				"Total Sales",
				SUM ( 
				'Sales'[Amount]
				)
			),
			[Total Sales]
		),
		'Sales'[Name],
		'Sales'[Name]
	) = TRUE (),
	[Name],
	"Other Cars"
)