Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Compare Year To Year Job Cost By Office - In Pivot ? Chart ?

Posted by: bakersburg9 Sep 11 2019, 03:17 PM

This question may be a bit vague, my data analysis skills / pivot table skills are lacking - but the sample data is attached - some key field in my data are AMOUNT, OFFICE and dates how would I show data for cost (AMOUNT) by office, grouped by years, on the cost, showing difference between years, like:

Office 2017 change 2018 change pct change 2019 pct change
Chicago 12,000 18,000 .50% 27,000 50%

Can this be done in a pivot ? charts ? the image is what I currently have in a Pivot...

 

 CostNoBilling_Lost_Jobs_WithProgFeeV2.zip ( 1.98MB ): 2
 

Posted by: MadPiet Sep 11 2019, 03:28 PM

Which version of Office 2013 do you have? If you have Pro, then PowerPivot is baked in. If not, I don't think it's available. (thanks, marketing!) If you do have PowerPivot, you can do it. If you know DAX, then there's an example at www.daxpatterns.com. If not, maybe Rob Collie's stuff would be a better place to start: www.powerpivotpro.com

If you want to build analytical models for this kind of thing, I think PowerPivot is the way to go. (You can always download PowerBI Desktop, too...)

Posted by: bakersburg9 Sep 11 2019, 03:31 PM

MadPiet:
Oops - just started working here - it's 2016 - put 2013 when I posted - yeah, I just RECENTLY heard of DAX - I went on the website, and couldn't find anything....

Steve

Posted by: MadPiet Sep 11 2019, 03:43 PM

Steve,

If you can DM me the file (strip out anything confidential... no need to overshare!), I can do a quick example with it and you can see what you can do with PowerPivot.
But I'd need unsummarized data, though.

Here's a super quick and dirty example of Sales Growth using PowerPivot/DAX...
Pieter

 Basic_DAX_YOY_Growth_stripped.zip ( 1.36MB ): 2
 

Posted by: bakersburg9 Sep 11 2019, 03:57 PM

Mad,

Done! Thanks so much !

Steve

Posted by: WildBird Sep 11 2019, 04:08 PM

Yeah, 2016 has more options for calculated fields. You could also code a dashboard (with formulas) that feeds from the pivots, I have done this before, not much issue there either.

Posted by: MadPiet Sep 11 2019, 04:27 PM

Steve,

Since I'm doing lots of asking... Any chance you have this as a database? Sorry, I'm feeling lazy after drywalling! Just MUCH quicker if I don't have to clean up the model.

Thanks!
Pieter

Posted by: bakersburg9 Sep 11 2019, 04:44 PM

Here you go - the query that's exported to Excel is q_CostNoBilling_Lost_Jobs_WithProgFee

 CostNoBilling_Lost_Job_Analysis_UA_Challenge.zip ( 1.36MB ): 3
 

Posted by: MadPiet Sep 11 2019, 05:33 PM

Steve,

here's part of it... If you're doing lots of analysis... slicing by various things, doing charts etc, then I would do this in PowerPivot. The kicker is that you have to get your head around DAX sooner or later. On the other hand, you can do REALLY cool stuff with DAX, once you get a basic handle on it. I read Rob Collie's book on DAX - the new one is like $30, and absolutely worth it.

There are a ton of videos on YouTube, too. This is one of the first channels I watched: https://chandoo.org/wp/about/ he has a channel on YouTube.

if you're doing Costing Analysis etc, DAX is your friend... check out the demo files on www.powerpivotpro.com (see the downloads section). Rob Collie worked with the Excel team for like 13 years, so he knows DAX inside and out. And he walks you through the stuff a little bit at a time, which is great.

If you want more financial type stuff, check out Sam McKay's stuff. (EnterpriseDNA).

Have fun!


 Bakersburg_Answer_1.zip ( 742.76K ): 3
 

Posted by: bakersburg9 Sep 11 2019, 05:49 PM

Mad - that's awesome! As you know, this is lost job cost, not sales, but that gives me an idea

Thanks! cool.gif

Steve

Posted by: MadPiet Sep 11 2019, 06:00 PM

Oh, so rename the measure, and you're on your way. =)
So negative numbers are good or bad?

If you want to get your head around DAX (and you need to in order to do this stuff), spend $30 on Rob Collie's book. TOTALLY worth it. The part that's weird about DAX is the Evaluation Context (the implicit filters on a measure) and overriding some/all of it using ALL and stuff. It's a bit mind-boggling, but once you understand some of the basic patterns (DAXPatterns.com), then you're well on your way. It's hard, no lie, but the stuff you can do with it with minimal effort is insane.

Posted by: MadPiet Sep 11 2019, 06:32 PM

Steve,

When you get around to it, could you classify the columns in your spreadsheet into groups, so all the related columns go together? I'm trying to figure out what this design is supposed to look like. the Loss data is at the center, then there's a Date dimension, but I'm not sure of much more than that. Once you have all the dimensions (tables you want to group/summarize by... kind of) worked out, then the model gets a LOT more interesting and useful.

If you download the sample files from Rob Collie's site (powerpivotpro.com), you can check out some of the later chapters (starting at about Chapter 8 maybe) and see some of the cool things you can do... % of Parent, etc.

Enjoy,

Pieter

Posted by: MadPiet Sep 13 2019, 11:04 AM

Found a helpful link for you
https://bifuture.blogspot.com/2015/09/dax-calculating-monthly-sales-changes.html

The other resource you may need:
https://powerpivotpro.com/the-book/

Posted by: bakersburg9 Sep 13 2019, 11:48 AM

Got some fires to put out, then I'll get on it - thanks !!! cool.gif

Posted by: MadPiet Sep 13 2019, 12:47 PM

Okay. Have fun!

One bit of advice, though... If you're diving into DAX, create simple measures first:

Total Losses := SUM('LossData'[Amount])

Then you can reuse existing calculations in new ones.

PY Losses := CALCULATE( [Total Losses], DATEADD('LossData'[TheDate],-1,YEAR) )

Then to get the delta:

Delta := [Total Losses] - [PY Losses]

Then the %

DIVIDE ( [Delta], [PY Losses], BLANK() )

Posted by: MadPiet Sep 13 2019, 04:44 PM

This one shows % of total and some other stuff.
Did you want Each Year's % of all years? (I think that's in there too)


 Bakersburg_Answer.zip ( 907.37K ): 1
 

Posted by: bakersburg9 Sep 13 2019, 04:59 PM

Just each years - but of course, there's been a "shift" in the wind, you know how that goes - but THANKS!!!

Posted by: MadPiet Sep 13 2019, 05:37 PM

The good news is that once you build a good data model, expanding it is super easy.

Holler if you get stuck again.

Posted by: bakersburg9 Sep 18 2019, 10:27 AM

MadPiet,
I REALLY appreciate you jumping in on this - I've attached the Excel workbook I'm updating, populating, whatever - I'm filling in columns H-L, and Col N if there are any notes - if you open the one Access report, you can see what's going into the excel workbook - there should be a way to do this in one shot, but I can't think of it - for now, I'm doing them one at a time, and am currently on row 1,243 at this time.

The objects in this Access database, for the most part, are just those which apply to this report, plus a few key ones - one thing that needs explaining is the configuration of the job number - nnn-nn-nnnnn - including the dash/hyphen, here's how it breaks down:

The 1st character is the year the job started
2nd / 3rd char: project director
4th: the first dash
5th: Office *
6th: Division (this is misleading - this is the type of remediation - Asbestos, Water damage, Fire)
7th: the second dash
8 thru 12: the sequential job order number - either starts with "0," or with "6" - this helps determine what office specifically, because many offices have the same number, like, for example, Chicago and Anaheim might be the same office number, but the way you tell what is the originating office is whether the last 5 of the job# starts with "0" or "6" - sounds crazy, but this allows them to keep all job numbers the same size


 2017___2018_All_Jobs_With_Costs_no_invoice.zip ( 17.81K ): 4
 CostNoBillingCopySep18.zip ( 618.57K ): 5
 

Posted by: MadPiet Sep 18 2019, 12:05 PM

Okay, now that I have something to play with, what questions were we trying to answer? <g>
Total Losses per time period, and % Growth?

---
Having played with this a little, the stuff in here is probably gonna shock some people.

Oh, and clean out your mailbox... it's full =)

Posted by: bakersburg9 Sep 18 2019, 12:15 PM

Well, first I'd like to see how to automate the process of updating the workbook, if possible - as for your question, that's where the game of "chicken" comes in - I don't know what my Sup wants, and I think he doesn't want to tell me he doesn't know what he wants - it's an odd dilemma - I'll never let this happen again!!! But I love, in theory, your idea of setting up a dummy account for the line items that the company basically "absorbs" - because, like I said, a lot of this activity is just cases where it's just a JOB - and the issue of no passing through/recouping expenses vis a vis invoices is irrelevant - just a job, that's it - a job with expenses, but it may not have to do with the companies' business, that of remediation.... Fire damage, flood, asbestos, etc. - say they are just doing a remodel to a plant - no need to do invoicing - that's just expenses...

Posted by: MadPiet Sep 18 2019, 01:56 PM

Automate the process of updating the workbook... you mean by requerying the database so that you pull in the new data? Go to the Data Tools tab, Click the green button "Go to PowerPivot Window", then click "Refresh"... it will re-run all the queries you have defined and import all the new data.

In PowerBi, you can schedule all that.


Posted by: bakersburg9 Sep 18 2019, 02:15 PM

No, I'm talking about how I'm inputting one at a time - like for Row 1298 on the Excel sheet named 'report1562013555682' I have to manually type in the amounts from AP, ATI subtotals, etc.

 

Posted by: MadPiet Sep 18 2019, 02:33 PM

What does the RowNumbers table mean? Are you grouping JobNumbers into groups of some kind? I don't understand the relationship between the two.

Posted by: bakersburg9 Sep 18 2019, 03:30 PM

QUOTE
What does the RowNumbers mean ?
It's pretty crazy - I don't, no I NEVER have worked like this - all the stuff with the row numbers and the alt job ID is just that the 2017 & 2018 All Jobs With Costs no invoice.xlsm is "The Bible" - there's not going to be any sorting/shifting - so the row numbers are just literally that - the row numbers in "The Holy Bible" - never to change - if you'll notice, the Access report I use to update the report1562013555682 tab in the "Bible" has row numbers referenced - as for the ALT job ID's, that's simply with / without dashes.

One thing I forgot to mention is that for some reason, not all these items have supporting documentation in Timberline (the construction software they use) - so those are to be skipped, but we don't want to lose the visibility/ tracking - the are to stay in the "Bible" - even though my process is pretty lame admittedly, it's nice that I can scroll through the Access report, and see the "breaks," where there's a row# missing, which probably means there's nothing in Timberline - so I can go in and just scan those in bunches, and confir

I cleared out my mailbox, so we can communicate that way, in Messenger