Creating sum column in dimension table

[Dunner2020]

Hi there,

I have a data model which looks as follow:

The daily activity table contains fine-grained data. Now I want to create two columns in the Master table which contains sum of EventDuration and NumberofConsumers columns of the Daily Activity table when both tables have the same EpochHH.

Same EpochHH Sum EventDuration =

CALCULATE(SUM('Daily Activity'[EventDuration]),FILTER('Daily Activity','Daily Activity'[EpochHH] = MAX('Master Table'[EpochHH]))

However, it did not produce the result. In fact it return blank row. Could you guide me where am I making the mistake?

Here is sample file from here

[jdbuchanan71]

@Dunner2020 

I think you want to go more like this for your model.  

Building a master table with every date/time combination (Master table) is definitely not a good way to do it.

You need to make sure your time fields are Date type: Time instead of Date/time

Then you would use measures against your daily activity with fields from your date and time tables:

Event Duration = SUM ('Daily Activity'[EventDuration] )

Consumers = SUM ( 'Daily Activity'[NumberOfConsumers] )

To get views of your data

I have attached my updated version of your sample for you to look at.

[Dunner2020]

@jdbuchanan71 thanks for the reply. However, I want to sum both columns in term of EpochHH and want to save in seperate table (such as Master Table).

[jdbuchanan71]

The create your master table like this.

Master Table = 
    ADDCOLUMNS (
        SUMMARIZE ( 'Daily Activity','Daily Activity'[EpochHH] ),
        "Duration", CALCULATE ( SUM ('Daily Activity'[EventDuration] ) ),
        "Consumers", CALCULATE ( SUM ( 'Daily Activity'[NumberOfConsumers] ) )
    )

[Dunner2020]

@jdbuchanan71 , the only issue with your proposed solution is that it only treats those EpcohHH that are present in the Daily activity table. However, I want to create a master table, which has EpochHH values that represent date from 1/1/2020 to 31/12/2025. So the EpochHH that represent dates that are not in daily activity should also be shown in the master table (although the values would be zeros for duration and consumers columns). Is there anyway that we can do that?   

[jdbuchanan71 · Accepted solution]

The problem you are having in your original view is the EpochHH in the master table is only in 30 min increments where the EpochHH on the 'Daily Activity' is in 1 minute increments.  If you fix your Master table to be in 1 min increments then this will sum all the amounts from the 'Daily Activity' onto the Master.

Event Duration = CALCULATE(SUM('Daily Activity'[EventDuration]))