Forum Discussion
Creating sum column in dimension table
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]))
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] ) )
)
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?
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]))