Aggregate / sum id based on 3 activities per day

[Anonymous]

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

[Jihwan_Kim · Accepted solution]

Hi,

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

[v-easonf-msft · Accepted solution]

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

[Anonymous · Accepted solution]

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

[v-easonf-msft · Accepted solution]

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

[Jihwan_Kim · Accepted solution]

Hi,

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