Summing Hourly Data from One Table with a Fixed Hour Block

[ryher]

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 the From Hour - To Hour columns.

Many thanks for all the help!

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[ryher]

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]

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]

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.