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