Forum Discussion

ryher's avatar
ryher
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago

Summing Hourly Data from One Table with a Fixed Hour Block

Hi all,   I am trying to aggregate impressions from an hourly table (left table), into a time block table (right table).   Essentially, I want to total the hourly data into the time blocks from t...
Need Help
ryher's avatar
ryher
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago

Hi,

The WeTransfer link to the sample data is here: https://we.tl/t-NDtWQ0vuaH

In one table, we have impressions broken down by the individual hour, but I'd like to group them up into their own time blocks.

For example, on Fridays, hours 0 = 108 impr, hours 1 = 126, and hours 2 = 125 in one table.

In another table, I have a "From Hour" and "To Hour" columns.
So the results for an "From Hour" = 0 and "To Hour" = 2 would say 359 impressions (108+126+125).

The two tables are linked by campaign name/campaign ID.

Happy to share any additional details.


 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

You have provided a little too much detail. Can you please indicate which two tables you mean?

The two tables are linked by campaign name/campaign ID.

Side note: you will want to do is to adjust your data model

Please identify which tables are facts and which are dimensions. Please do not use bidirectional search unless there is a dire need for it.

  • ryher's avatar
    ryher
    Icon for Microsoft Employee rankMicrosoft Employee
    3 years ago

    The two tables are the (BAM - Campaign Target Import) table and the (UI - Ad Group by Hour) table.
    Is it possible to have a measure that returns a table of numbers between two numbers back?
    Like From 0hr - 2hr returns [0,1,2].
    With those numbers, I can sum the impressions across those hours (hour 0 + hour 1 + hour 2).

    A new WeTransfer link has been created with the updated table names & relationships: https://we.tl/t-nD5ekyhbFG

    Eventually, I would like for the (BAM - Ad Group Target Import) table to talk back with the (UI - Ad Group by Hour) table, but I expect the logic for the (BAM - Campaign Target Import) table can be used for the (BAM - Ad Group Target Import) table.

    Feel free to delete/unlink the (Campaign Key), (Ad Group Key), and (Hour of Day Key) tables - those were made to help the BAM tables talk to the (Ad Group by Hour) table, but I'm open to a better way to have those table talk directly to each other.