Forum Discussion

MonsterATS's avatar
MonsterATS
Regular Visitor
4 years ago
Solved

Standard Deviation from a Cumulative Measure

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's avatar
    v-yalanwu-msft
    4 years ago

    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.

3 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      MonsterATS
      Regular Visitor
      4 years ago

       

       

      LocationProductionMonths Producing
      A251
      A502
      A703
      A754
      B151
      B402
      B703
      B804
      C501
      C602

      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:

       

      A145
      B125

       

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

       

      A75
      B55
      C110

       

      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.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.