Calculation on Grouped Values

[theitguy]

Hi guys,

I would like to create a measure which calculates the standard derivation of sums from a group-by action.

I have the following table:

launch_history_id	Date	Seconds_Spend	Score	Participant_ID
22484	17-11-2017	444	0,6	Learner1
24771	23-11-2017	24	0	Learner1
24773	23-11-2017	974	0,57	Learner1
22470	17-11-2017	439	1	Learner1
24801	23-11-2017	1	0	Learner1
22435	17-11-2017	84	0	Learner2
23319	20-11-2017	28	0	Learner2
23317	20-11-2017	72	0	Learner2
22483	17-11-2017	373	0,5	Learner2
22481	17-11-2017	10	0	Learner2
22487	17-11-2017	225	0	Learner3
24878	23-11-2017	148	1	Learner4
24886	23-11-2017	4	1	Learner4
24887	23-11-2017	0	0	Learner4
24902	23-11-2017	15	0	Learner4
24905	23-11-2017	141	0	Learner4
24920	23-11-2017	53	1	Learner4
24921	23-11-2017	4	1	Learner4
22323	17-11-2017	17	0	Learner4
24924	23-11-2017	183	1	Learner4
24931	23-11-2017	3	1	Learner4
24929	23-11-2017	48	0	Learner4
24932	23-11-2017	94	0	Learner4
22456	17-11-2017	836	1	Learner4
22332	17-11-2017	411	0	Learner5
22434	17-11-2017	150	0,0204082	Learner6

First,I would like to group this data by Participant_ID and sum the seconds spend to get someting like that:

Learner1	1882
Learner2	567
Learner3	225
Learner4	1546
Learner5	411
Learner6	150

Now I would like to calculate the standard derivation for these values. (STD:669,07)

Can I do this with the help of a single measure?

Thank you!

[Zubair_Muhammad · Accepted solution]

HI @theitguy

Try this MEASURE

Measure =
VAR MyColumn =
    SUMMARIZE (
        TableName,
        TableName[Participant_ID],
        "TotalSeconds", SUM ( TableName[Seconds_Spend] )
    )
RETURN
    STDEVX.P ( MyColumn, [TotalSeconds] )

[Zubair_Muhammad]

@theitguy

File attached too

[theitguy]

@Zubair_Muhammad

Thank you very much! I did not know that I can assign tables to variables as well. 🙂