Create sum column in dimension table

DAX DAX Calculations
1

Hi there,

I have a data model which looks as follow:

image
image955×594 27.3 KB

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: sample7.pbix (2.5 MB)

2

Hi @leo_89,
I see no reason for you to use a calculated column. Your can just use two measures that show the total event duration and total number of customers for any given EpochHH number (I assume that’s what you want to achieve). If you mean another thing please explain more. thanks.

sample7.pbix (2.5 MB)

3

@HASSAN_BI thanks for your reply. I am interested in creating columns, not the measure. The reason is that these values are important in my calculations and these columns will be the foundation of many other measures.

4

@leo_89,
okay i get. You, but if you just one the duration and number of customers why did you use the filter EpochHH = Max ('EpochHH) ? why didnt you just use :

Same EpochHH Sum EventDuration = CALCULATE(SUM(‘Daily Activity’[EventDuration]) ) ?

sample7.pbix (2.4 MB)

5

@HASSAN_BI,

I also tried CALCULATE(SUM(‘Daily Activity’[EventDuration]) ) in master table and did not work.

6

@leo_89,
Did u check out the last pbix i uploaded. i used calculate (sum (total duration)) it seems to be working.