Pivot Chart Help

[ch33748]

I need help in creating a pivot chart displaying the total revenue expected per month over all jobs.

The data would need to be subtotaled per phase to get the correct data but I want to quickly capture it in a pivot chart.

TASK	ASSIGNED TO	SOV	SOV REMAINING	PROGRESS	START	END	DAYS	REV PER DAY
COURTHOUSE	JOHN	$ 95,000.00	$ 47,500.00	50%	1-Jan-23	23-Sep-23	266
	U/G STORM ROUGH PHASE III	JOHN	$ 45,000.00	$ 9,000.00	80%	13-Mar-23	2-Jun-23	82	$ 548.78
	U/G DOMESTIC WATER	JOHN	$ 35,000.00	$ 17,500.00	50%	20-Feb-23	15-Apr-23	55	$ 636.36
	U/G STORM ROUGH PHASE IV	JOHN	$ 15,000.00	$ 13,500.00	10%	15-May-23	7-Aug-23	85	$ 176.47
MIDDLE SCHOOL	BILL	$ 92,350.00	$ 80,806.25	13%	1-Nov-22	26-Sep-23	330	$ 279.85
	AREA A	BILL	$ 20,000.00	$ 11,000.00	45%	7-Jul-22	27-Jun-23	356	$ 56.18
	AREA B	BILL	$ 15,000.00	$ 10,500.00	30%	21-Jul-22	14-Jul-23	359	$ 41.78
	AREA C	BILL	$ 30,000.00	$ 22,500.00	25%	4-Aug-22	8-Aug-23	370	$ 81.08
	AREA D	BILL	$ 10,000.00	$ 9,000.00	10%	9-Jun-22	28-Jun-23	385	$ 25.97
	AREA E	BILL	$ 2,500.00	$ 2,125.00	15%	12-May-22	15-May-23	369	$ 6.78
	AREA F	BILL	$ 3,000.00	$ 2,700.00	10%	21-Apr-22	25-Sep-23	523	$ 5.74
	AREA G	BILL	$ 1,850.00	$ 1,757.50	5%	31-Mar-22	13-Mar-23	348	$ 5.32
	OUTBUILDINGS	BILL	$ 10,000.00	$ 9,500.00	5%	18-Aug-22	28-Sep-23	407	$ 24.57

 

[awoohaw]

what column defines the "job" and what column (or calculation) defines the expected revenue per month?

 

[ch33748]

what column defines the "job" and what column (or calculation) defines the expected revenue per month?

Task = job
SOV = revenue for entire date range
Rev per day = revenue broken down per day

 

[ch33748]

Task = job
SOV = revenue for entire date range
Rev per day = revenue broken down per day

Mainly creating a forecast of expected revenue per month

 

[awoohaw]

the above does not define how you want expected monthly revenue calculated.

 

[awoohaw]

you seem to have different values in some columns: In Assigned to you have Names in the first row of each section, then some kind of code.
In the Start column you have a date, then percentages in subsequent rows. In the Days you have an integer, then a bunch of dates.

 

[awoohaw]

your data seems like a formatted report and not a data set. You need to get it into a flat file format or a two flat files with common elements linking.

 

[ch33748]

your data seems like a formatted report and not a data set. You need to get it into a flat file format or a two flat files with common elements linking in this

So my formatting is set up so I can put a dollar next to each portion of a particular job. I’m tracking multiple things on this sheet at the same time.

% is for my team to track job completion status. Not necessary for this task.

Days is just a tracker of how many days to complete the project.

I was probably going to eliminate some other columns if I can get this to populate.

 

[awoohaw]

Okay. It is much easier to have flat data (everything on one row) and develop all reporting from that. It will take a long time to figure out how to get what you want, but it is doable. But, the downside is that if you want a new type of report it will take just as long to create as the first one.

Do you have source data that this comes from? You may also be able to use power query to turn this into a flat file.

 

[ch33748]

Okay. It is much easier to have flat data (everything on one row) and develop all reporting from that. It will take a long time to figure out how to get what you want, but it is doable. But, the downside is that if you want a new type of report it will take just as long to create as the first one.

Do you have source data that this comes from? You may also be able to use power query to turn this into a flat file.

I don’t have a source data currently. I have to manually entered per job because that’s the schedule I receive and it’s easier to plug in that and play with numbers because the schedule is dictated to us.