1 Reply Latest reply: Sep 8, 2017 2:27 AM by Shraddha Gajare RSS

    Compare Actual Data with Plan Data

    Justin Dallas

      Hello Everyone,

       

      TL;DR How to correctly architect my data structure to compare Actual Activity vs. Planned/Modeled Activity.

       

      I have a datamodel where I have 4 tables,  DriverProfile, FactTable, MasterCalendar and the DriverModel.  The DriverModel table contains how many miles a driver is expected to drive on a certain day.  The actual miles the driver drives are on the FactTable.  My goal is to compare the Actual Miles driven to the DriverModel miles.

       

      As of now, I have a bar chart that looks like this.

       

      The dimension is:  =If( [Driver Model Id] = [Driver Profile Id], [Driver Model Id] )

      The measures are: Sum([Driver Model Miles])

                                     Sum([OrderMiles])

       

      DriverCapture1.PNG

       

      This gives me the result I'm expecting.  But when I use real data, it gets painfully slow, and the CPU hits 90+% any time the chart is rendered.  I believe it has to do with the way my measure is "If( [Driver Model Id] = [Driver Profile Id], [Driver Model Id] )"


      Is there a better way to do this? I'm not able to figure out how to arrive at the same result, and getting a bigger machine (4 core, 16GB currently) is out of the question.


      I've attached my script and the QVF.  Any help is greatly appreciated.