topic Re: Aggregate / sum id based on 3 activities per day in DAX Commands and Tips

Aggregate / sum id based on 3 activities per day

Anonymous — Thu, 23 Jun 2022 18:47:23 GMT

Hello,

I have this table:

id	date	activity	activity_count
308	1-Jun-22	a	312
308	1-Jun-22	c	39
308	1-Jun-22	b	80
308	2-Jun-22	c	251
308	2-Jun-22	b	222
308	3-Jun-22	a	50
308	3-Jun-22	c	223
308	3-Jun-22	b	171
308	4-Jun-22	b	302
308	4-Jun-22	c	106
308	4-Jun-22	a	23

I am a bit stuck with how to do the following:

I would like to generate a table or measure that sums the "activity_count" for the 3 activities "activity" (a, b and c), per day, per id

Example: For id 308, the sum of the 3 activities (a, b and c) is 312+39+80 = 431, resulting in one line for this:

308

1-Jun-22

431

This is then repeated per day, per id

308	1-Jun-22	431
308	2-Jun-22	402
941	3-Jun-22	235

Any ideas?

Cheers,

Carsten

Re: Aggregate / sum id based on 3 activities per day

Jihwan_Kim — Fri, 24 Jun 2022 03:10:53 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Re: Aggregate / sum id based on 3 activities per day

v-easonf-msft — Tue, 28 Jun 2022 10:07:03 GMT

Hi， Anonymous

Try steps as below:

1.enter a table "Activity Table" as below:

2. add a measure like:

Result = IF ( SELECTEDVALUE ( 'Activity Table'[activity] ) IN VALUES ( 'Table'[activity] ), CALCULATE ( SUM ( 'Table'[activity_count] ), FILTER ( 'Table', 'Table'[activity] = MAX ( 'Activity Table'[activity] ) ) ), IF ( SELECTEDVALUE ( 'Activity Table'[activity] ) = "Sum", CALCULATE ( SUM ( 'Table'[activity_count] ) ), BLANK () ) )

3.apply this new field to a table visual

Best Regards,
Community Support Team _ Eason

Re: Aggregate / sum id based on 3 activities per day

Anonymous — Thu, 07 Jul 2022 08:33:08 GMT

Dear all,

Thanks a lot for all your input (very much appreciated) and sorry for my late response. In the end, while working on the report, I have ended up creating a table dedicated to this (data qualit scores), supported by a group by function.

Cheers, Carsten