Standard Deviation from a Cumulative Measure

[MonsterATS]

I have dozens of locations with different start dates.  For each location I have monthly productivity.  Using a start date and a separate table of 'Months On Production' I can compare locations by months on production instead of date.  I created a measure for cumulative production, which enables me to look at cumulative production, by location, by months on production.  Normally, I compare wells by one month at a time.  For example, I may look at month 12 and see what **bleep** production is by each location.  If I pick a short date, say 6 months, I might have dozens of locations.  If I pick something longer, like 48 months, I might only have a few locations that have been producing that long.

How do I calculate a standard deviation on the cumulative total for only the month that I've selected.  Right now, if I select a specific month I get the standard deviation for the monthly production for all the locations, not the standard deviation for the cumulative total.

[v-yalanwu-msft · Accepted solution]

Hi， @MonsterATS ；

You could create a measure then use STDEVX.P() .

1.create a CumProduction measure.

CumProduction =
CALCULATE (
    SUM ( 'Table'[Production] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Location] ),
        [Months Producing] <= MAX ( 'Table'[Months Producing] )
    )
)

2.calculate stdevx.

stdevx = STDEVX.P(ALLEXCEPT('Table','Table'[Months Producing]),[CumProduction])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @MonsterATS 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[MonsterATS]

Location	Production	Months Producing
A	25	1
A	50	2
A	70	3
A	75	4
B	15	1
B	40	2
B	70	3
B	80	4
C	50	1
C	60	2

This table above (tblProduction) would be similar to what I am looking at.  There is other information, but none of it relative to this discussion.  There is another table called tblMonthsProducing that has a series of numbers from 1-1000.  The tblMonthsProducing is joined to tblProduction through "Months Producing".  To calculate the cumulative production I have this formula:  CumProduction =IF(ISBLANK(SUM([Production),BLANK(),CALCULATE(SUM[Production],FILTER(ALL(tblMonthsProducing),tblMonthsProducing[Months Producing]<=MAX(tblMonthsProducing[Months Producing])))).

On my report I have a Table output and a slicer.  Using the example of 3 months.  When I set the slicer to 3 I will get the following output:

A	145
B	125

If I set the slicer to 2 I will get the following output:

A	75
B	55
C	110

This all works properly.  What I am struggling with is a way to calculate the standard deviation of the cumulative numbers that are output each time.  The goal is to display this as one number in a Visualization Table.  With the numbers given above, the first example would be the Standard Deviation of 145 and 125 (So a table with one number: 10).  The second example would be the Standard Deviation of 75, 55, and 110 (which would be a table with one number: 22.7).  If I try and use one of the STD DEV functions BI calculates a deviation based on the individual lines of tblProduction for each location, not on the cumulative measure.

The numbers above are a simplified version of what I'm analyzing.  There are dozens of locations that could factor into an evaluation, depending on the length of time I am looking at a location having been in service.