Adding measures in a data model at a summary level - how ?

%3CLINGO-SUB%20id%3D%22lingo-sub-1250622%22%20slang%3D%22en-US%22%3EAdding%20measures%20in%20a%20data%20model%20at%20a%20summary%20level%20-%20how%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1250622%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20probably%20not%20going%20to%20describe%20this%20very%20well%20-%20but%20here%20goes%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20to%20data%20modeling%20and%20am%20trying%20to%20convert%20old%20files%20%2F%20pivot%20tables%20to%20new%20table%20design%2Fdata%20model%20structures%20and%20at%20the%20same%20time%20develop%20some%20new%20reports%20for%20key%20stakeholders.%26nbsp%3B%20The%20attached%20file%20has%20a%20table%20(I%20know%20its%20not%20optimal%20in%20terms%20of%20structure%20-%20but%20way%20better%20than%20it%20was).%26nbsp%3B%20This%20table%20(called%20Hours_New)%20collects%20employee%20time%20daily.%26nbsp%3B%20The%20time%20is%20regular%2C%20ot%20or%20unpaid.%26nbsp%3B%20It%20is%20also%20chargeable%20or%20non-chargeable%20(as%20determined%20by%20the%20column%20in%20the%20table%20titled%20SC%20%26amp%3B%20CD%20Combo.%26nbsp%3B%20There%20are%20productivity%20targets%20(50%25%20for%20labourers%2C%2075%25%20for%20other%20trades).%26nbsp%3B%20What%20I%20want%20to%20be%20able%20to%20do%20is%20report%20the%20productivity%20%25%20at%20various%20levels%20(Foreman%2C%20Trade%2C%20Time%20Period%20etc%20etc%20etc).%26nbsp%3B%20When%20I%20build%20pivot%20tables%20to%20try%20and%20do%20this%20I'm%20not%20able%20to%20get%20the%20results%20presented%20as%20I%20want%20them%20-%20I've%20taken%20a%20couple%20of%20stabs%20at%20it%2C%20but%20the%20results%20are%20way%20too%20busy%20%26amp%3B%20I%20want%20to%20be%20able%20to%20highlight%20when%20targets%20are%20being%20missed%20(on%20both%20upper%20and%20lower%20ends).%26nbsp%3B%20I'm%20sure%20its%20not%20all%20that%20tough%20to%20do%20but%20I'm%20spinning%20my%20wheels%20-%20grrr.%26nbsp%3B%20The%20attached%20file%20has%20multiple%20tabs%20and%20I've%20trimmed%20the%20dataset%20down%20-%20its%20actually%20close%20to%20500%2C000%20rows%20and%20removed%20most%20of%20the%20other%20pivot%20tables%20not%20related%20to%20what%20I'm%20trying%20to%20do%20-%20any%20help%20would%20be%20most%20appreciated%20!!%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1250622%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Contributor

I'm probably not going to describe this very well - but here goes

 

I am new to data modeling and am trying to convert old files / pivot tables to new table design/data model structures and at the same time develop some new reports for key stakeholders.  The attached file has a table (I know its not optimal in terms of structure - but way better than it was).  This table (called Hours_New) collects employee time daily.  The time is regular, ot or unpaid.  It is also chargeable or non-chargeable (as determined by the column in the table titled SC & CD Combo.  There are productivity targets (50% for labourers, 75% for other trades).  What I want to be able to do is report the productivity % at various levels (Foreman, Trade, Time Period etc etc etc).  When I build pivot tables to try and do this I'm not able to get the results presented as I want them - I've taken a couple of stabs at it, but the results are way too busy & I want to be able to highlight when targets are being missed (on both upper and lower ends).  I'm sure its not all that tough to do but I'm spinning my wheels - grrr.  The attached file has multiple tabs and I've trimmed the dataset down - its actually close to 500,000 rows and removed most of the other pivot tables not related to what I'm trying to do - any help would be most appreciated !!  

 

 

0 Replies