Using summary value from one level below RRS feed

  • Question

  • The fields below are from a transaction level table along with a quick sample of data:

    Actual Date, Phase of Work, Cost Type, Actual Cost, Actual Quantity

    Example Data:

    '10', '7/1/15', '100.1', 'L', 150.00, 10
    '10', '7/1/15', '100.1', 'E', 125.00, 10
    '10', '7/2/15', '100.1', 'L', 175.00, 15
    '10', '7/2/15', '100.1', 'E', 145.00, 14
    '10', '7/3/15', '100.1', 'L', 200.00, 18
    '10', '7/3/15', '100.1', 'E', 165.00, 18
    '10', '7/4/15', '100.1', 'L', 110.00, 8
    '10', '7/4/15', '100.1', 'E', 100.00, 8

    As you can see each transaction is recorded based on a Job, Date, Phase of Work, Cost Type.  There are indeed other fields like transaction number and so forth, but those don't matter.  What matters is the Quantity is recorded at the Cost Type Level. 

    So in the above example for Phase of Work '100.1' and Cost Type 'L' you have a total quantity of 51.  And for the same phase of work, but cost type 'E' you have a total quantity of 50.  In the way we do job costing they really should be the same, but they could be different like in the example... due to either an error on the end user's point of view or maybe a second cost type was added later in the job.

    In the end the quantity is used to represent the production quantity at the "Cost Type" level as well as the "Phase of Work" production quantity.

    So technically the production quantity for the cost type is just the summed total of quantity for the Job / Phase / Cost Type.  BUT... what is the production quantity for the Job / Phase.  It is NOT the summed quantity of all the Cost Types typed to that Phase.   It is the summed quantity of ONE of those Cost Types.  There is a table holding the Job / Phase / Cost Type and there is a flag stating which cost type is the "MASTER" for tracking quantity for the Phase itself.

    In our example above cost type 'L' is designated the "MASTER" for tracking quantity for phase '100.1'.  So using that example we would consider the following accurate:

    Phase '100.1' has a production quantity of 51 since cost type 'L' sums quantity to be 51 and is marked as the master for tracking quantity.  For reporting purposes not only do we consider 51 as the accurate value for the Phase of Work quantity our reports see 51 as the accurate quantity for all Cost Types associated to phase '100.1'.

    In the reporting world and standard queries this is easy for us to deal with.  In the end this example on a report would show Phase of Work for '100.1' as having a production quantity of 51 and the cost type 'L' AND cost type 'E' as having a quantity of 51 since 'L' is the master and we would throw an exception report at the end stating there is a discrepancy in the quantity at the cost type level so they can go back later and make an adjustment if they desire.  Even if they don't make the correction the reports will calculate unit costs correctly based on the master cost type's production quantity.  We typically don't show the transaction level quantities, but almost always have it more of a summary so the user wouldn't see the actual quantity at a transaction level and the report just shows what we designate as the production quantity based on the above.

    Now in the BI world and Pivot Tables I am not certain how to make this work... especially when you are down at the transaction level.  If I do nothing other than create summary values you would get what you would expect.  The quantity for '100.1' / 'L' would be 51 and '100.1' / 'E' would be 50 and then it would show the quantity for '100.1' as begin 101 which is incorrect.  In this case it should be 51 since cost type 'L' for this phase is marked as the master.

    1) So in this example what calculation could we use to summarize the phase of work's production to be based on the summed quantity of it's master cost type????  I don't believe it is critical we make the other cost types match the master cost type, BUT we must make the phase production quantity match the master cost type.

    2) And for fear of complicating it further... IF someone forgot to mark one of the phase of work's cost types as a master then we (in typical reporting) consider the larger value the more accurate value.  So in the example above if phase 100.1 didn't not have a specific cost type marked as it's "MASTER" for production tracking we would have just grabbed the largest summed quantity which in this case does happen to be cost type 'L'. 

    In the scheme of things if we could get 1 to work then we could setup exception reports to make sure they are indeed always marking a "MASTER" cost type, but option 2 would be nice in the event one is missed just so the report does make a calculation based on the largest summed cost type and still spit out an exception report.

    I have looked at seeing what I could do at the transaction level when I query the data for the data model.  If I had a different granularity (summed up to the cost type level) I could alter the query so it would look like I want, but at the transaction level I couldn't see a way to alter the values to work for a pivot table.

    I know this is a little confusing, but hopefully it was able to convey the issue.  Here is a final image that may help:


    • Edited by gsaunders Monday, July 13, 2015 2:03 PM
    Monday, July 13, 2015 1:38 PM


  • Here is what ended up working:

    PeriodActualQty:=IF(HASONEVALUE(Dim_JCJobPhasesCT[CostTypeDescription]) || HASONEVALUE(Dim_JCJobPhasesCT[CostType]),
    [TempPeriodActualQty], CALCULATE([TempPeriodActualQty], FILTER(Dim_JCJobPhasesCT, Dim_JCJobPhasesCT[PhaseUnitFlag]="Y")))

    In the above we are basically saying if we are at a level where there is ONLY value for CostTypeDescription or CostType then use the field [TempPeriodActualQty] which is a simple SUM.  Otherwise there are multiple values and we are at the Phase level so do the same SUM calculation, BUT we will filter it on the cost type marked as the master flag.  So in the original image in my original post the 870 now shows 290 as it should.

    Where it will fail at the moment is if there is NOT a master flag set which could happen so I really need to expand further to accommodate that situation.  

    But for now I consider this a good answer IF we can require the master flag be set on at least one cost type of each phase before the user can save the phase in the application.

    • Marked as answer by gsaunders Wednesday, July 15, 2015 1:01 PM
    Wednesday, July 15, 2015 1:01 PM