Pivot Table

[zinah]

Hi,

I have below table and not sure how can I set this table to produce a pivot table that has Rows=Location & Columns = Function.

Location	Function1	Function2	Function3	Function4	Function5	Function6	Function7	Total	Year
Loc1	0	0	0	1	4	0	0	5	2018
Loc2	4	1	0	5	21	0	0	31	2018
Loc3	0	0	0	0	1	0	0	1	2018
Loc4	0	0	0	0	3	0	0	3	2018
Loc5	0	0	0	0	1	0	0	1	2018
Loc6	0	0	0	0	5	0	0	5	2019
Loc7	0	0	0	0	1	0	0	1	2019
Loc8	0	0	0	1	1	0	0	2	2019
Loc9	0	0	0	2	0	0	0	2	2019
Loc10	0	0	0	0	4	0	0	4	2019

As the current table can produce only below pivot table:

Row Labels	Sum of Total
Loc1	5​
Loc10	4​
Loc2	31​
Loc3	1​
Loc4	3​
Loc5	1​
Loc6	5​
Loc7	1​
Loc8	2​
Loc9	2​
Grand Total	55​

 

[sandy666]

like this?

Sum of Value		Attribute
Year	Location	Function1	Function2	Function3	Function4	Function5	Function6	Function7	Grand Total
2018	Loc1	0	0	0	1	4	0	0	5
	Loc2	4	1	0	5	21	0	0	31
	Loc3	0	0	0	0	1	0	0	1
	Loc4	0	0	0	0	3	0	0	3
	Loc5	0	0	0	0	1	0	0	1
2019	Loc10	0	0	0	0	4	0	0	4
	Loc6	0	0	0	0	5	0	0	5
	Loc7	0	0	0	0	1	0	0	1
	Loc8	0	0	0	1	1	0	0	2
	Loc9	0	0	0	2	0	0	0	2
Grand Total		4	1	0	9	41	0	0	55

or

Location	Sum of Value
Loc1	5
Loc10	4
Loc2	31
Loc3	1
Loc4	3
Loc5	1
Loc6	5
Loc7	1
Loc8	2
Loc9	2
Grand Total	55

or

Location	Attribute	Sum of Value
Loc1	Function1	0
	Function2	0
	Function3	0
	Function4	1
	Function5	4
	Function6	0
	Function7	0
Loc10	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	4
	Function6	0
	Function7	0
Loc2	Function1	4
	Function2	1
	Function3	0
	Function4	5
	Function5	21
	Function6	0
	Function7	0
Loc3	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	1
	Function6	0
	Function7	0
Loc4	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	3
	Function6	0
	Function7	0
Loc5	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	1
	Function6	0
	Function7	0
Loc6	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	5
	Function6	0
	Function7	0
Loc7	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	1
	Function6	0
	Function7	0
Loc8	Function1	0
	Function2	0
	Function3	0
	Function4	1
	Function5	1
	Function6	0
	Function7	0
Loc9	Function1	0
	Function2	0
	Function3	0
	Function4	2
	Function5	0
	Function6	0
	Function7	0
Grand Total		55

or I misunderstood

 

[zinah]

like this?

Sum of Value		Attribute
Year	Location	Function1	Function2	Function3	Function4	Function5	Function6	Function7	Grand Total
2018	Loc1	0	0	0	1	4	0	0	5
	Loc2	4	1	0	5	21	0	0	31
	Loc3	0	0	0	0	1	0	0	1
	Loc4	0	0	0	0	3	0	0	3
	Loc5	0	0	0	0	1	0	0	1
2019	Loc10	0	0	0	0	4	0	0	4
	Loc6	0	0	0	0	5	0	0	5
	Loc7	0	0	0	0	1	0	0	1
	Loc8	0	0	0	1	1	0	0	2
	Loc9	0	0	0	2	0	0	0	2
Grand Total		4	1	0	9	41	0	0	55

or

Location	Sum of Value
Loc1	5
Loc10	4
Loc2	31
Loc3	1
Loc4	3
Loc5	1
Loc6	5
Loc7	1
Loc8	2
Loc9	2
Grand Total	55

or

Location	Attribute	Sum of Value
Loc1	Function1	0
	Function2	0
	Function3	0
	Function4	1
	Function5	4
	Function6	0
	Function7	0
Loc10	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	4
	Function6	0
	Function7	0
Loc2	Function1	4
	Function2	1
	Function3	0
	Function4	5
	Function5	21
	Function6	0
	Function7	0
Loc3	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	1
	Function6	0
	Function7	0
Loc4	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	3
	Function6	0
	Function7	0
Loc5	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	1
	Function6	0
	Function7	0
Loc6	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	5
	Function6	0
	Function7	0
Loc7	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	1
	Function6	0
	Function7	0
Loc8	Function1	0
	Function2	0
	Function3	0
	Function4	1
	Function5	1
	Function6	0
	Function7	0
Loc9	Function1	0
	Function2	0
	Function3	0
	Function4	2
	Function5	0
	Function6	0
	Function7	0
Grand Total		55

or I misunderstood

Like the first option

 

[zinah]

like this?

Sum of Value		Attribute
Year	Location	Function1	Function2	Function3	Function4	Function5	Function6	Function7	Grand Total
2018	Loc1	0	0	0	1	4	0	0	5
	Loc2	4	1	0	5	21	0	0	31
	Loc3	0	0	0	0	1	0	0	1
	Loc4	0	0	0	0	3	0	0	3
	Loc5	0	0	0	0	1	0	0	1
2019	Loc10	0	0	0	0	4	0	0	4
	Loc6	0	0	0	0	5	0	0	5
	Loc7	0	0	0	0	1	0	0	1
	Loc8	0	0	0	1	1	0	0	2
	Loc9	0	0	0	2	0	0	0	2
Grand Total		4	1	0	9	41	0	0	55

or

Location	Sum of Value
Loc1	5
Loc10	4
Loc2	31
Loc3	1
Loc4	3
Loc5	1
Loc6	5
Loc7	1
Loc8	2
Loc9	2
Grand Total	55

or

Location	Attribute	Sum of Value
Loc1	Function1	0
	Function2	0
	Function3	0
	Function4	1
	Function5	4
	Function6	0
	Function7	0
Loc10	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	4
	Function6	0
	Function7	0
Loc2	Function1	4
	Function2	1
	Function3	0
	Function4	5
	Function5	21
	Function6	0
	Function7	0
Loc3	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	1
	Function6	0
	Function7	0
Loc4	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	3
	Function6	0
	Function7	0
Loc5	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	1
	Function6	0
	Function7	0
Loc6	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	5
	Function6	0
	Function7	0
Loc7	Function1	0
	Function2	0
	Function3	0
	Function4	0
	Function5	1
	Function6	0
	Function7	0
Loc8	Function1	0
	Function2	0
	Function3	0
	Function4	1
	Function5	1
	Function6	0
	Function7	0
Loc9	Function1	0
	Function2	0
	Function3	0
	Function4	2
	Function5	0
	Function6	0
	Function7	0
Grand Total		55

or I misunderstood

This is what I get when I do the pivot table, not sure why the function name disappears:

 

[sandy666]

use Power Query (Get&Transform)
Data - From Table
---
in Power Query Editor:
select all Function columns
Unpivot only selected columns
Close & Load to - Connection only
----
Insert Pivot Table
use external data source - choose Query - (your table name)
choose location for your Pivot Table
then Year and Location into ROWS Area
Attribute into the COLUMNS Area
Value into the VALUES Area

 

[sandy666]

or you can add Totals Row to your Excel Table and define what you want in Totals Row (without Pivot Table)

Location	Function1	Function2	Function3	Function4	Function5	Function6	Function7	Total	Year
Loc1	0	0	0	1	4	0	0	5	2018
Loc2	4	1	0	5	21	0	0	31	2018
Loc3	0	0	0	0	1	0	0	1	2018
Loc4	0	0	0	0	3	0	0	3	2018
Loc5	0	0	0	0	1	0	0	1	2018
Loc6	0	0	0	0	5	0	0	5	2019
Loc7	0	0	0	0	1	0	0	1	2019
Loc8	0	0	0	1	1	0	0	2	2019
Loc9	0	0	0	2	0	0	0	2	2019
Loc10	0	0	0	0	4	0	0	4	2019
Total	4	1	0	9	41	0	0	55

for Year choose - none
for the rest - sum

 

[zinah]

or you can add Totals Row to your Excel Table and define what you want in Totals Row (without Pivot Table)

Location	Function1	Function2	Function3	Function4	Function5	Function6	Function7	Total	Year
Loc1	0	0	0	1	4	0	0	5	2018
Loc2	4	1	0	5	21	0	0	31	2018
Loc3	0	0	0	0	1	0	0	1	2018
Loc4	0	0	0	0	3	0	0	3	2018
Loc5	0	0	0	0	1	0	0	1	2018
Loc6	0	0	0	0	5	0	0	5	2019
Loc7	0	0	0	0	1	0	0	1	2019
Loc8	0	0	0	1	1	0	0	2	2019
Loc9	0	0	0	2	0	0	0	2	2019
Loc10	0	0	0	0	4	0	0	4	2019
Total	4	1	0	9	41	0	0	55

for Year choose - none
for the rest - sum

That helped a lot, as I couldn't find Power Query Editor, and this solution is simpler, thank you so much for your help!

 

[sandy666]

You are welcome
I am glad you are happy

btw. update your profile about excel version (Account Details) - less troubles and wasted posts