UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Compare Year To Year Job Cost By Office - In Pivot ? Chart ?, Office 2013    
 
   
bakersburg9
post Sep 11 2019, 03:17 PM
Post#1



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


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...
This post has been edited by bakersburg9: Sep 11 2019, 03:18 PM
Attached File(s)
Attached File  CostNoBilling_Lost_Jobs_WithProgFeeV2.zip ( 1.98MB )Number of downloads: 2
Attached File  OffceCostYearToYearComparisonPIvot.png ( 7.25K )Number of downloads: 0
 
Go to the top of the page
 
MadPiet
post Sep 11 2019, 03:28 PM
Post#2



Posts: 3,361
Joined: 27-February 09



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...)
Go to the top of the page
 
bakersburg9
post Sep 11 2019, 03:31 PM
Post#3



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


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
This post has been edited by bakersburg9: Sep 11 2019, 03:34 PM
Go to the top of the page
 
MadPiet
post Sep 11 2019, 03:43 PM
Post#4



Posts: 3,361
Joined: 27-February 09



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
This post has been edited by MadPiet: Sep 11 2019, 04:03 PM
Attached File(s)
Attached File  Basic_DAX_YOY_Growth_stripped.zip ( 1.36MB )Number of downloads: 2
 
Go to the top of the page
 
bakersburg9
post Sep 11 2019, 03:57 PM
Post#5



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Mad,

Done! Thanks so much !

Steve
Go to the top of the page
 
WildBird
post Sep 11 2019, 04:08 PM
Post#6


UtterAccess VIP
Posts: 3,636
Joined: 19-August 03
From: Auckland, Little Australia


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.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
MadPiet
post Sep 11 2019, 04:27 PM
Post#7



Posts: 3,361
Joined: 27-February 09



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
Go to the top of the page
 
bakersburg9
post Sep 11 2019, 04:44 PM
Post#8



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Here you go - the query that's exported to Excel is q_CostNoBilling_Lost_Jobs_WithProgFee
This post has been edited by bakersburg9: Sep 11 2019, 04:45 PM
Attached File(s)
Attached File  CostNoBilling_Lost_Job_Analysis_UA_Challenge.zip ( 1.36MB )Number of downloads: 3
 
Go to the top of the page
 
MadPiet
post Sep 11 2019, 05:33 PM
Post#9



Posts: 3,361
Joined: 27-February 09



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!

Attached File(s)
Attached File  Bakersburg_Answer_1.zip ( 742.76K )Number of downloads: 3
 
Go to the top of the page
 
bakersburg9
post Sep 11 2019, 05:49 PM
Post#10



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


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

Thanks! cool.gif

Steve
Go to the top of the page
 
MadPiet
post Sep 11 2019, 06:00 PM
Post#11



Posts: 3,361
Joined: 27-February 09



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.
Go to the top of the page
 
MadPiet
post Sep 11 2019, 06:32 PM
Post#12



Posts: 3,361
Joined: 27-February 09



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
Go to the top of the page
 
MadPiet
post Sep 13 2019, 11:04 AM
Post#13



Posts: 3,361
Joined: 27-February 09



Found a helpful link for you
https://bifuture.blogspot.com/2015/09/dax-c...es-changes.html

The other resource you may need:
https://powerpivotpro.com/the-book/
Go to the top of the page
 
bakersburg9
post Sep 13 2019, 11:48 AM
Post#14



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Got some fires to put out, then I'll get on it - thanks !!! cool.gif
Go to the top of the page
 
MadPiet
post Sep 13 2019, 12:47 PM
Post#15



Posts: 3,361
Joined: 27-February 09



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() )
Go to the top of the page
 
MadPiet
post Sep 13 2019, 04:44 PM
Post#16



Posts: 3,361
Joined: 27-February 09



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)

Attached File(s)
Attached File  Bakersburg_Answer.zip ( 907.37K )Number of downloads: 1
 
Go to the top of the page
 
bakersburg9
post Sep 13 2019, 04:59 PM
Post#17



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Just each years - but of course, there's been a "shift" in the wind, you know how that goes - but THANKS!!!
Go to the top of the page
 
MadPiet
post Sep 13 2019, 05:37 PM
Post#18



Posts: 3,361
Joined: 27-February 09



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

Holler if you get stuck again.
Go to the top of the page
 
bakersburg9
post Sep 18 2019, 10:27 AM
Post#19



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


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

This post has been edited by bakersburg9: Sep 18 2019, 10:59 AM
Attached File(s)
Attached File  2017___2018_All_Jobs_With_Costs_no_invoice.zip ( 17.81K )Number of downloads: 4
Attached File  CostNoBillingCopySep18.zip ( 618.57K )Number of downloads: 5
 
Go to the top of the page
 
MadPiet
post Sep 18 2019, 12:05 PM
Post#20



Posts: 3,361
Joined: 27-February 09



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 =)
This post has been edited by MadPiet: Sep 18 2019, 01:02 PM
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    19th November 2019 - 01:53 PM