How to show TopN + "Other" Group

[bullius]

Hi,

I am analysing expenditure. I want to use a pie chart to show % expenditure, but there are too many groups:

I want to keep the largest 6 groups, then group the remaining as "Other":

I want this group to be dynamic, so that if I change it to Income, it will perform a similar operation.

Something with the following logic:

If [Group] is not one of the TopN, then "Other", else [Group]

Anyone know how this can be done?

Thanks!

[datamvt]

Big Thanks to ZhongTr0n for his solution : https://towardsdatascience.com/power-bi-add-category-other-to-charts-354267ec1809 

[Rashna]

Hi,

I have the similar requirement .I have got my top N. However I could not get "Others" group. 
I would be grateful if someone can please help me.

Thanks!

[bullius]

I have tried the following formulas:

Based on TopN:

Dynamic Group = 
IF ( 
	CONTAINS ( 
		TOPN (
			5,
			SUMMARIZE ( 
				'Table1',
				[Group],
				"Sum Amount",
				SUM ( 
				'Table2'[Amount]
				)
			),
			[Sum Amount]
		),
		'Table1'[Group],
		'Table1'[Group]
	) = TRUE (),
	[Group],
	"Other"
)

Based on Top %:

Dynamic Group = 
IF ( 
	[%] < 0.05,
	"Other",
	[Title A]
)

Both work, but they don't respond to slicers or filters, so are not dynamic.

Any thoughts?

[bullius]

It might be a case of working in a FILTER by ALLSELECTED function somewhere, but I have tried a few different combinations and all end up with an error.

[v-caliao-msft]

Hi @bullius,

In order to get Other group, we can use RANKX function to achieve this requirement.

NewGroup= IF(RANKX(Table3,Table3[Amount])<=5,Table3[Category],"Others")

Regards,

Charlie Liao

[bullius]

Thanks @v-caliao-msft,

I can't seem to fit my data into your formula.

My data is set up like the example below:

'Transactions'

Transaction	Code	Group	Type	Amount £
A11	A1	A	Income	11
A12	A1	A	Income	11
A21	A2	A	Income	55
A22	A2	A	Income	55
A31	A3	A	Income	22
A32	A3	A	Income	22
B11	B1	B	Income	88
B12	B1	B	Income	88
B21	B2	B	Income	66
B22	B2	B	Income	66
B31	B3	B	Income	33
B32	B3	B	Income	33
C11	C1	C	Income	2
C12	C1	C	Income	2
D11	D1	D	Income	6
D12	D1	D	Income	6
E11	E1	E	Income	10
E12	E1	E	Income	10
F11	F1	F	Expenditure	10
F12	F1	F	Expenditure	10
F21	F2	F	Expenditure	50
F22	F2	F	Expenditure	50
F31	F3	F	Expenditure	20
F32	F3	F	Expenditure	20
G11	G1	G	Expenditure	80
G12	G1	G	Expenditure	80
G21	G2	G	Expenditure	60
G22	G2	G	Expenditure	60
G31	G3	G	Expenditure	30
G32	G3	G	Expenditure	30
H11	H1	H	Expenditure	1
H12	H1	H	Expenditure	1
I11	I1	I	Expenditure	3
I12	I1	I	Expenditure	3
J11	J1	J	Expenditure	5
J12	J1	J	Expenditure	5

'Chart_of_Accounts'

Code	Group	Group Name
A1	A	Aa
A2	A	Aa
A3	A	Aa
B1	B	Bb
B2	B	Bb
B3	B	Bb
C1	C	Cc
D1	D	Dd
E1	E	Ee
F1	F	Ff
F2	F	Ff
F3	F	Ff
G1	G	Gg
G2	G	Gg
G3	G	Gg
H1	H	Hh
I1	I	Ii
J1	J	Jj

With this example, the visualised data looks like this:

I would want groups Jj, Ii and Hh to be grouped as "Other".

Also, it would need to be subject to the Type slicer.

Could you see if you can adjust the RANKX formula to accomodate this data setup?

Thanks!

[v-caliao-msft]

@bullius，

I tested it on my locla environment by using your sample data.

Firstly, create a new table:
Table = SUMMARIZE('Transaction','Transaction'[Group],'Transaction'[Type],"TotalAmount",SUM('Transaction'[Amount £]))

Then get group name from Chart_of_Accounts table

GroupName = LOOKUPVALUE(Chart_of_Accounts[Group Name],Chart_of_Accounts[Group],'Table'[Group])

Then use RANK function to get others group.

NewGroupName = IF(RANKX(FILTER('Table','Table'[Type]=EARLIER('Table'[Type])),'Table'[TotalAmount])<=2,'Table'[GroupName],"Others")

Regards,

Charlie Liao

[bullius]

Thanks @v-caliao-msft. That works perfectly with the example data I gave. The key being the EARLIER function, which I had not considered.

However, I want to go a step further.

My data contains more columns that I want to use to filter the data. I didn't include them in the example because I wanted to keep it simple and hoped the solution would work with more than the example columns.

The problem is, If I use the EARLIER function to group the ranked data by each given column, I will need a NewGroupName column for every concievable filter combination.

Ultimately, my question is this: Can the values in a calculated column change based on slicer selection?

If they can, then I would need a formula for the NewGroupName column that would give the TopN Groups and "Others" group, whether I am filtering by Income / Expenditure, any of the other columns, or a combination.

If they can't, then I will make it work with your NewGroupName formula, using the EARLER function.

Thank you for bearing with me.

[Anonymous]

I'm going to be working on a similar problem. I have not tried FILTER by ALL SELECTED yet, so have been relying on Measures to ensure that calculations are dynamic. I don't think the Groups idea is going to help you with this. Groups are good for some things and terrible for others, and definitely not dynamic.

[Anonymous]

Hello Evryone, 

Some one have a news about this topic ?

Best Regards

[Anonymous]

Hi,

Have you achieved top n+ others group...i have the similar requirement and looking for the soultion..pls help me if u achieved it

Thanks,
Rajeev

[bullius]

No progress, sorry.

[Anonymous]

Have you tried grouping or binning? 

 https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning