Need help with Pivot

[vvillanueva]

Hello!

For context, I'm quite inexperience with Pivot table and I was wondering if someone would be able to help me out with my dilemma

So basically I'm trying to generate a report among our 3 divisions. And I need a pivot table which would show how many jobs were booked per division and per type on a monthly basis, broken down into range of amounts (<25K; 25K-100K; 100K-1m; >1M)

Can that be done using the table I provided?

[63falcondude]

Your data is not set up in a way that is most efficient to be analyzed.

The best way to format this raw data to be used in a pivot table would be to have the following columns: Division, Type, Month, Value

We can make this adjustment in Power Query, build your custom range columns in Power Pivot, and then build a pivot table from that.

Import data into PQ:
Click on any of the cells in your data > Data > From Table/Range > OK

Make transformation into clean data to be used by pivot:
Select Division and Type columns > Transform > Unpivot Columns > Unpivot Other Columns
Add Column > Columns From Examples > type a few dates out manually (1/1/2021, 2/1/2021, etc) until the formula picks up on the logic > OK
Change the name of the custom calculation to "Month" and the formatting to Date (click the ABC to the left of the header cell)
Delete the Attribute column

Close Power Query and load your data as a connection:
Home > Close & Load To > Only Create Connection > Check the "Add this data to the Data Model" box > OK

Create measures for custom value groupings:
Data > Manage Data Model (you may need to enable Power Pivot) > in the cells below the data, enter these formulas:

<25K:=calculate(COUNT(Table1[Value]),Table1[Value]<25000)
25K-100K:=calculate(COUNT(Table1[Value]),Table1[Value]>=25000,Table1[Value]<100000)
100K-1M:=calculate(COUNT(Table1[Value]),Table1[Value]>=100000,Table1[Value]<1000000)
>=1M:=calculate(COUNT(Table1[Value]),Table1[Value]>=1000000)

Close the Power Pivot window.

On a new sheet, insert a pivot table, and drag Division and Type into Rows, Month into columns, and the 4 measures that we created into Values.
Then drag Values from Columns to Rows

You can then format the pivot table however you'd like.

See attached.

[vvillanueva]

Your data is not set up in a way that is most efficient to be analyzed.

The best way to format this raw data to be used in a pivot table would be to have the following columns: Division, Type, Month, Value

We can make this adjustment in Power Query, build your custom range columns in Power Pivot, and then build a pivot table from that.

Import data into PQ:
Click on any of the cells in your data > Data > From Table/Range > OK

Make transformation into clean data to be used by pivot:
Select Division and Type columns > Transform > Unpivot Columns > Unpivot Other Columns
Add Column > Columns From Examples > type a few dates out manually (1/1/2021, 2/1/2021, etc) until the formula picks up on the logic > OK
Change the name of the custom calculation to "Month" and the formatting to Date (click the ABC to the left of the header cell)
Delete the Attribute column

Close Power Query and load your data as a connection:
Home > Close & Load To > Only Create Connection > Check the "Add this data to the Data Model" box > OK

Create measures for custom value groupings:
Data > Manage Data Model (you may need to enable Power Pivot) > in the cells below the data, enter these formulas:

<25K:=calculate(COUNT(Table1[Value]),Table1[Value]<25000)
25K-100K:=calculate(COUNT(Table1[Value]),Table1[Value]>=25000,Table1[Value]<100000)
100K-1M:=calculate(COUNT(Table1[Value]),Table1[Value]>=100000,Table1[Value]<1000000)
>=1M:=calculate(COUNT(Table1[Value]),Table1[Value]>=1000000)

Close the Power Pivot window.

On a new sheet, insert a pivot table, and drag Division and Type into Rows, Month into columns, and the 4 measures that we created into Values.
Then drag Values from Columns to Rows

You can then format the pivot table however you'd like.

See attached.

This is awesome. Like I said, I'm inexperienced with pivot in general.

Follow-up question though, on the first step (import data to PQ), when I select the cells, do I include the headers or just the data?

I'm trying to follow your tutorial so I learn how to do it myself.

[63falcondude]

Include the headers.

[vvillanueva]

Include the headers.

Sounds good!

Quick question: Let's say I want to split the data into two sheets (SERVICE and CONSTRUCTION), can I create a single pivot table for the two sheets?

[JeteMc]

It sounds as if what you want could be accomplished using Power Pivot providing the sets of data will have a common field.
That said, it may be easier to answer the question if we could see a sample of the SERVICE and CONSTRUCTION sheets.
Let us know if you have any questions.