cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ciervax
Frequent Visitor

Group columns on pivot table

Hello to every one,

 

I've been trying to group certain columns depending on a value.

 

I have this table:

 

ciervax_0-1618328917873.png

 

 

 

 

PRODUCTFLOWPERIODDECLARANTPARTNERQUANTITYVALUE
13Jan. 2000GermanyAustralia5855800
13Jan. 2000GermanyUK6589800
13Jan. 2000GermanyUSA24512500
13Jan. 2000GermanyCanada32415200
13Jan. 2000GermanyBrazil2153600
13Jan. 2000GermanyArgentina6853666
13Jan. 2000GermanyFrance69823111
13Jan. 2000GermanyItaly523625555
13Jan. 2000GermanyChile255445556
13Jan. 2000GermanyMexico255445558
13Jan. 2000GermanyJapan2125445800

 

 

 

I've created a pivot table, all partners/countries are shown, but I want to group certain countries into a "column" inside the pivot table as "others".

This is what I'm trying to get:

 

ciervax_0-1618328879092.png

 

PARTNERAUSTRALIABRAZILUSACANADAOTHERS
PERIODQTYVALUEQTYVALUEQTYVALUEQTYVALUEQTYVALUE
Jan. 2000585580021536002451250032415200  

 

 

The column other should be the sum of the countries which are not shown, those countries will be always the same ones, does not depend on a certain value of qty or value.

 

Many thanks in advance!

1 ACCEPTED SOLUTION

Hi @ciervax 

I tried @JW_van_Holst 's solution and create a pivot table like below. The output is the same as your expected result. Is this what you want?

041605.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

6 REPLIES 6
JW_van_Holst
Resolver III
Resolver III

In power query add an column:

Capture.PNG

In your pivot table select _Partner in stead of Partner and it should do the job.
Good luck,

//JW

Thank you for your help.

 

The problem I see, is the new column _Partner is marking all fields as others, even if the country in partner is not listed as "Australia", "etc" 

 

Then if I select _Partner as a column instead of Partner, is showing only the column Others cause the column is containing all countries.

I don't know why it is not reading the list properly, and all rows are "Others".


Hi @ciervax 

I tried @JW_van_Holst 's solution and create a pivot table like below. The output is the same as your expected result. Is this what you want?

041605.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

Rocco_sprmnt21
Super User II
Super User II

if you upload a copiable table, could, perhaps, get you a quicker response. 

Sure, I've edited the post, I tried the first time to do it, but the second table was not showing properly, cause some fields are combined, I don't know if there is any other option.

in power query it is not possible, as far as i know, to have two columns with the same name in merged cells (like in excel).

So this is, in my opinion, the one that comes closest to the layout you are looking for.

Prova e facci sapere.

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors