cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bullius Member
Member

How to show TopN + "Other" Group

Hi,

 

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

 

Pie.png

 

 

 

 

 

 

 

 

 

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

 

Pie.png

 

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!

12 REPLIES 12
bullius Member
Member

Re: How to show TopN + "Other" Group

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 Member
Member

Re: How to show TopN + "Other" Group

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.

 

 

Moderator v-caliao-msft
Moderator

Re: How to show TopN + "Other" Group

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")

Capture.PNGCapture1.PNG

 

Regards,

Charlie Liao

bullius Member
Member

Re: How to show TopN + "Other" Group

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'

TransactionCodeGroupTypeAmount £
A11A1AIncome11
A12A1AIncome11
A21A2AIncome55
A22A2AIncome55
A31A3AIncome22
A32A3AIncome22
B11B1BIncome88
B12B1BIncome88
B21B2BIncome66
B22B2BIncome66
B31B3BIncome33
B32B3BIncome33
C11C1CIncome2
C12C1CIncome2
D11D1DIncome6
D12D1DIncome6
E11E1EIncome10
E12E1EIncome10
F11F1FExpenditure10
F12F1FExpenditure10
F21F2FExpenditure50
F22F2FExpenditure50
F31F3FExpenditure20
F32F3FExpenditure20
G11G1GExpenditure80
G12G1GExpenditure80
G21G2GExpenditure60
G22G2GExpenditure60
G31G3GExpenditure30
G32G3GExpenditure30
H11H1HExpenditure1
H12H1HExpenditure1
I11I1IExpenditure3
I12I1IExpenditure3
J11J1JExpenditure5
J12J1JExpenditure5

 

'Chart_of_Accounts'

CodeGroupGroup Name
A1AAa
A2AAa
A3AAa
B1BBb
B2BBb
B3BBb
C1CCc
D1DDd
E1EEe
F1FFf
F2FFf
F3FFf
G1GGg
G2GGg
G3GGg
H1HHh
I1IIi
J1JJj

 

With this example, the visualised data looks like this:
Grouping.png
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!

 

Moderator v-caliao-msft
Moderator

Re: How to show TopN + "Other" Group

@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")

 

Capture.PNGCapture1.PNGCapture2.PNG

 

Regards,

Charlie Liao

 

bullius Member
Member

Re: How to show TopN + "Other" Group

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.

rajeevbikkani Regular Visitor
Regular Visitor

Re: How to show TopN + "Other" Group

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 Member
Member

Re: How to show TopN + "Other" Group

No progress, sorry.

dennisj Regular Visitor
Regular Visitor

Re: How to show TopN + "Other" Group

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 480 members 4,512 guests
Please welcome our newest community members: