Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Standard Deviation

Hi All, My Fact has ItemKey,CompanyKey,MonthKey,Revenue is there anway to calculate 'StDev.P' with resepect to itemnumbers from Item Dimension and MonthNumbers from Date Dimension. where revenue f...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    Anonymous

     

    I would personally prefer to do this with a measure rather than creating any intermediate tables:

    Something like:

    =
STDEVX.P (
    SUMMARIZE ( 'Fact', DimItem[ItemNumber], DimDate[MonthNumber] ),
    CALCULATE ( SUM ( 'Fact'[Revenue] ) )
)

    This should produce the same result as Eric_Zhang had above.

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
9 years ago

Anonymous

 

Instead of calculating the standard deviation in the summized table, create an extra measure.

 

Stdev.P = Stdev.P('A'[Revenue])

 

Drag the columns and the measure into a table visual and it would show a non-filtered standard deviation rather than a sum up value.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Eric_Zhang,

    Thanks for the reply.

    i did not get any.

    At least can we do this?

    can we feed StDev.P or StDevX.P function with MonthNumber and ItemNumber.

    If we have Itemkey,MonthKey,CompanyKey,Revenue

    Joined with Item,Date,Company Dimensions using keys

    Is there anyway we write dax

    StDev.P(Fact[Revenue]) for ItemNumber,MonthNumber

    Irrespective of rows with companies.

    Because if i have itemnumber,monthnumber,Revenue in a table we get 12 rows

    but if 1 add Company to it then i get 36 rows and because of which my standard deviation function taking 36 values instead of 12 Summary values which i explained earlier.

    Please consider the data above.

    • OwenAuger's avatar
      OwenAuger
      Super User
      9 years ago

      Anonymous

       

      I would personally prefer to do this with a measure rather than creating any intermediate tables:

      Something like:

      =
STDEVX.P (
    SUMMARIZE ( 'Fact', DimItem[ItemNumber], DimDate[MonthNumber] ),
    CALCULATE ( SUM ( 'Fact'[Revenue] ) )
)

      This should produce the same result as Eric_Zhang had above.

      • srb803's avatar
        srb803
        Frequent Visitor
        2 years ago

        I am working on a similar scenario and have hierarchies of the categories. 
        It worked for me at the top level.

    • Eric_Zhang's avatar
      Eric_Zhang
      Microsoft Employee
      9 years ago

      Anonymous

       

      Have you tried the measure? Not sure if I get all your idea, however the measure works in a context of the leading columns, regardless of the rows in your table.  If your add or remove the leading columns(ItemNumber,CompanyCode), the measure varies accordingly.