Have some columns not aggregate in Pivot Table

[ilya2004]

Hello all,

I am trying to create a pivot table where I am displaying success rate (displayed as %). I would like to be able to sort on the left hand side by country descending by total employee population and across the top I have some business segments (dummy table below).

My problem that when I use business segment as a column value in the pivot table, it will then try to aggregated both the success rate AND the population size. However, I don't care about the population in each segment, I only care about their success rate, and I would like the population to be aggregated totally for the whole country (so that I can sort by large countries).

I can think of some hacks to do this, but I wanted to see if there was a real way to do this within pivot table configuration.

I hope that makes sense.

	Sum of Population	Sales	Services	Operations
Canada	700	9%	80%	42%
Romania	690	100%	16%	39%
China	680	23%	76%	20%
Saudi Arabia	670	29%	44%	6%
Brazil	660	98%	58%	34%
Australia	650	30%	57%	7%
Germany	640	79%	54%	71%
Israel	630	72%	50%	34%
Spain	620	55%	6%	45%
Indonesia	610	50%	37%	33%

 

[hernantorres23]

Hi, put the origin of the table, this is the result

 

[ilya2004]

I don't have an origin, it's just dummy data. You can imagine any table that looks like this, which I then try to aggregate

Name	Country	Business Segment	Success Rate
Jack	Canada	Sales	80%​
John	China	Services	95%​
Luke	Canada	Sales	91%​
Sam	Brazil	Operations	25%​
Pat	Israel	Services	100%​
Charlie	Canada	Sales	87%​

 

[hernantorres23]

Now, I understand you, well an easy way for do this is to use Power Query

Book1
	A	B	C	D	E
1	Table origin (for me ventas)
2	Name	Country	Business Segment	Success Rate
3	Jack	Canada	Sales	80%
4	John	China	Services	95%
5	Luke	Canada	Sales	91%
6	Sam	Brazil	Operations	25%
7	Pat	Israel	Services	100%
8	Charlie	Canada	Sales	87%
9
10
11
12			Ctrl + t
13
Sheet20

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	This is the table from Power Query						Your example result
2	Name	Country	Success Rate	Attribute	Value			Sum of Population	Sales	Services	Operations
3	Jack	Canada	0.8	Business Segment	Sales		Canada	700	9%	80%	42%
4	John	China	0.95	Business Segment	Services		Romania	690	100%	16%	39%
5	Luke	Canada	0.91	Business Segment	Sales		China	680	23%	76%	20%
6	Sam	Brazil	0.25	Business Segment	Operations		Saudi Arabia	670	29%	44%	6%
7	Pat	Israel	1	Business Segment	Services		Brazil	660	98%	58%	34%
8	Charlie	Canada	0.87	Business Segment	Sales		Australia	650	30%	57%	7%
9							Germany	640	79%	54%	71%
10							Israel	630	72%	50%	34%
11							Spain	620	55%	6%	45%
12							Indonesia	610	50%	37%	33%
13
14
15
16
17
18
19
20							This is the pivot table result
21							Sum of Success Rate		Value
22							Country	Name	Operations	Sales	Services
23							Brazil	Sam	25.00%
24							Canada	Charlie		87.00%
25								Jack		80.00%
26								Luke		91.00%
27							China	John			95.00%
28							Israel	Pat			100.00%
29
Sheet21

let
    Source = Excel.CurrentWorkbook(){[Name="ventas"]}[Content],
    #"Need_columns" = Table.Unpivot(Source, {"Business Segment"}, "Attribute", "Value")
in
    #"Need_columns"

Here, I share an Example