Standard Deviation and Average of Count of Occurrences

[Anonymous]

I am currently building a PowerBI to display how many pumps of a product you get out of a certain bottle of product. Many replicates are being done to determine the variation in number of pumps per bottle. I have time stamps for each occurance and an index value for which bottle these pumps are made on. I want to the average and standard deviation of the total number of pumps per bottle. There will be 100+ bottles, so I don't want to have to write 100+ measures for each bottle. Any ideas of how to write a measure so I can display the updated average and standard deviation as more and more data comes in? I put an example of the data set I have below. Thank you for your help!

Date Time	Pump	Bottle Index
7/30/2020 6:08	1	5
7/30/2020 6:09	1	5
7/30/2020 6:11	1	5
7/30/2020 6:14	1	5
7/30/2020 6:15	1	5
7/30/2020 6:16	1	5
7/30/2020 6:18	1	5
7/30/2020 6:19	1	5
7/30/2020 6:20	1	5
7/30/2020 6:22	1	5
7/30/2020 6:23	1	6
7/30/2020 6:24	1	6
7/30/2020 6:26	1	6
7/30/2020 6:27	1	6
7/30/2020 6:28	1	6
7/30/2020 6:30	1	6
7/30/2020 6:31	1	6
7/30/2020 6:32	1	6
7/30/2020 6:34	1	6
7/30/2020 6:35	1	6
7/30/2020 6:36	1	6
7/30/2020 6:38	1	6
7/30/2020 6:39	1	6
7/30/2020 6:40	1	7
7/30/2020 6:41	1	7
7/30/2020 6:43	1	7
7/30/2020 6:44	1	7
7/30/2020 6:45	1	7
7/30/2020 6:47	1	7
7/30/2020 6:48	1	7
7/30/2020 6:49	1	7
7/30/2020 6:51	1	7
7/30/2020 6:52	1	7
7/30/2020 6:53	1	8
7/30/2020 6:55	1	8
7/30/2020 6:56	1	8
7/30/2020 6:57	1	8
7/30/2020 6:59	1	8
7/30/2020 7:00	1	8
7/30/2020 7:01	1	8
7/30/2020 7:03	1	8
7/30/2020 7:04	1	8
7/30/2020 7:05	1	8

[Anonymous · Accepted solution]

[Avg Across Bottles] =
    AVERAGEX(
        VALUES( T[Bottle Index] ),
        CALCULATE(
            SUM( T[Pump] )
        )
    )
    
[Std Across Bottles] =
    STDEVX.S(
        VALUES( T[Bottle Index] ),
        CALCULATE(
            SUM( T[Pump] )
        )        
    )

[Anonymous]

That worked, thank you!!!

[Anonymous]

[Avg] = AVERAGE( T[Pump] )
[Std] = STDEV.S( T[Pump] )

// All you have to do now is
// slice by Bottle Index to
// get the avg and std of
// the total number of pumps
// per bottle.

[Anonymous]

If I use:

[Avg] = AVERAGE( T[Pump] )
[Std] = STDEV.S( T[Pump] )

It produces an Avg = 1, and Std = 0. All pump values are 1 in my table. I want to do the average and std based on the sum of "1"s from each bottle index value (5-8 in my example). The answer should be:

Average = 10.75

Std.S = 1.5

Which comes from:

Index	Bottle Pumps
5	10
6	13
7	10
8	10

I need a way to somehow transform my data into table like this within PowerBI and then calculate average and standard deviation from this new table. 

[amitchandak]

@Anonymous , refer if this can help

https://www.edureka.co/community/31365/to-calculate-the-standard-deviation-in-power-bi

https://www.youtube.com/watch?v=ekcrlfZyAhg

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageKey=b2097958-4252-4979-b9f4-627aaf0fcda2&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer&ReturnUrl=%2Fcommunities%2Fcommunity-home%2Fdigestviewer%3FCommunityKey%3Db35c8468-2fd8-4e1a-8429-322c39fe7110

[Anonymous]

I couldn't find anything in those links to help with my application. If you have any specific advice or code that would help with my problem listed above would be helpful, thanks!