Pivot Table Help!!!!

[Island Girl]

[h=2]

Help with pivot table[/h]

Good morning,
how do i create a pivot table for the following:

1. Compare all four location showing the each weekly and Monthly values.
2. Compare the monthly KWH for each location.
3. Compare the values for locations y and a.


location x

week	number of persons	estimated kwh
1-6 nov 16	19
7-13 nov 16	38
14-20 nov 16	27
21-27 nov 16	20
28-27 nov 16	15
Month Total		38,000

<tbody>
</tbody>

Location y

week	number of persons	estimated kwh
1-6 nov 16	5
7-13 nov 16	5
14-20 nov 16	6
21-27 nov 16	5
28-27 nov 16	5
Month Total		15,000

<tbody>
</tbody>

Location z

week	number of persons	estimated kwh
1-6 nov 16	10
7-13 nov 16	10
14-20 nov 16	12
21-27 nov 16	11
28-27 nov 16	13
Month Total		44,000

<tbody>
</tbody>

Location a

week	number of persons	estimated kwh
1-6 nov 16	27
7-13 nov 16	35
14-20 nov 16	33
21-27 nov 16	39
28-27 nov 16	30
Month Total		55,000

<tbody>
</tbody>

Your assistance is greatly needed!!!!!​

 

[cyrilbrd]

Hi, you might want to consider changing the way your raw data is encoded.
For instance one single table containing the fields Location, Week, Number of persons, estimated kWh might be easier to work with.

 

[Anand Sharma]

Hi,

Normalize your tables into one with additional field identifying Data for locations as below and then use pivot table to compare

Location	week	number of persons	estimated kwh
A	1-6 nov 16	27
X	1-6 nov 16	27
Y	1-6 nov 16	27

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[propi_]

Hi,

Normalize your tables into one with additional field identifying Data for locations as below and then use pivot table to compare

Location	week	number of persons	estimated kwh
A	1-6 nov 16	27
X	1-6 nov 16	27
Y	1-6 nov 16	27

<tbody>
</tbody>

Hi by normalizing Anand means putting it in one big table with the location column and then load it up to Power Pivot.

Then create the Measure :

=Sum([estimated kwh])

regards
Andy