Pivot Table Data from multiple columns into one

[Jessica553]

Hello, I'm trying to create a pivot table to show the values of different payroll codes by state. But the data that I have is in one row. I have a formula to split it into separate columns, but I can't get this into a pivot table without looking very ugly. I want it to say something similar to below and filter by EOM date. So I want all the of 'Classification' columns to come under a single column in the pivot table. Any help is much appreciated!

QLD
	WAGE	Total sum for QLD
	SALARY	Total sum for QLD
	NO	Total sum for QLD
	SUPER	Total sum for QLD
	EXEMPT	Total sum for QLD
NSW

 

[Alex Blakenburg]

I am struggling to understand what you want to finish up with.

Using Design > Report Layout > Tabular form, I get this.

20221006 Pivot Table Jessica553.xlsx
	A	B	C
13	State	LC Classification	Sum of LC Amount
14	NSW	Exempt	8.00
15		Super	2.00
16	NSW Total		10.00
17	QLD	Exempt	10.00
18		Super	7.00
19	QLD Total		17.00
20	VIC	Super	1.00
21	VIC Total		1.00
22	Grand Total		28.00
Data

 

[Jessica553]

The problem is that because the data is on the same row, it's showing them in sub headings. In your example, it's only summing up LC Classification. I want it to show every classification (EC, DC and LC) as though they were their own separate items. IE not reliant on other items in the row.

 

[Jessica553]

At the moment it looks like the below screenshot, but I need the other items from dc and lc to be their own lines and not dependent on the first one.

 

[Alex Blakenburg]

I'm afraid I don't understand. Can you show me what you want it to look like ?

 

[RoryA]

Do you have Power Query available to you?