Mysterious Measures RRS feed

  • Question

  • Hi all,

    I'm relatively new to PowerPivot, so this question is probably going to make you laugh. Anyway, I'd appreciate it if someone could help me on this.

    I have 2 data tables. One shows the amount of clicks on a website in 5-minute buckets (per day), the other one shows the amount of seconds of TV spots (also in 5-minute buckets per day).

    I want to create a pivot table combining these two, making the amount of clicks and the seconds of TV spots comparable (essentially trying to figure out the impact of the TV spots).

    I've linked the two datetime columns in the PowerPivot window.

    However, when i create a pivot table, using the datetime column of the TV spots as rows (sorry, using German excel, not sure about the 100% translation) and adding clicks and seconds as values, only the seconds are porperly summed up. The clicks are displayed as the overall total of all clicks. Vice-versa if I use the datetime column of the "clicks" table (clicks are properly calculated, minutes shows as overall total).

    I KNOW I need to insert a Measure somewhere, but I can't figure out what this measure should calculate in order to display the clicks properly calculated (or the seconds).
    Thursday, December 5, 2013 12:42 PM


  • Long story short, for this, you should create a separate master Date/Time dimension. And then both the datetime columns (in clicks and TVSpots) should be linked to the master DateTime dimension. Hide the datetime columns in Clicks and TVSPots (optional, just for usability purpose) and now use only the datetime column from the master table. Now both the tables will sum up properly.

    The underlying principle of this is creating efficient data models in Power Pivot using lookup tables.

    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Monday, December 9, 2013 8:08 PM