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 for each and every month numbers will be the values to calculate 'Stdev.P' for individual item.

they should not respect Companykey in the fact as if it consider company then 1 item may repeat in different companies and will calculate stdev.p for individual company which i dont want.

 

Let’s be specific.

I have Fact table with

Fact(ItemKey,CompanyKey,MonthKey,Revenue)

DimDate(YearKey,MonthKey,MonthNumber)

DimItem(ItemKey,ItemNumber,ItemName)

DimCompany(CompanyKey,CompanyCode,CompanyName)

Created  Summary table as follows:

  1. Method (without filters)

A=Summarize(‘Fact’,DimItem[ItemNumber],DimDate[MonthNumber],”Revenue”,SUM(‘Fact’[Revenue]))

B=Summarize(‘A’,’A’[ItemNumber],”Revenue”,Sum(‘A’[Revenue]),Stdev.P(‘A’[Revenue]))

‘A’ Summary for 1 Item

ItemNumber

MonthNumber

Revenue

2016A

1

1326

2016A

2

1207

2016A

3

1102

2016A

4

1243

2016A

5

855

2016A

6

885

2016A

7

962

2016A

8

818

2016A

9

965

2016A

10

880

2016A

11

833

2016A

12

1464

 

‘B’ Summary for 1 Item

ItemNumber

StDev.P

2016A

208.55

 

 

 2. Method (I need company filter to be applied on the table so I am bringing CompanyCode also)

A=Summarize(‘Fact’,DimItem[ItemNumber],DimCompany[CompanyCode],DimDate[MonthNumber],”Revenue”,SUM(‘Fact’[Revenue]))

B=Summarize(‘A’,’A’[ItemNumber],’A’[CompanyCode],”Revenue”,Sum(‘A’[Revenue]),Stdev.P(‘A’[Revenue]))

‘A’ Summary for 1 Item

ItemNumber

CompanyCode

MonthNumber

Revenue

2016A

B

1

18

2016A

B

2

20

2016A

B

3

16

2016A

B

4

31

2016A

B

5

16

2016A

B

6

20

2016A

B

7

26

2016A

B

8

39

2016A

B

9

28

2016A

B

10

33

2016A

B

11

20

2016A

B

12

16

2016A

A

1

167

2016A

A

2

189

2016A

A

3

199

2016A

A

4

296

2016A

A

5

179

2016A

A

6

214

2016A

A

7

249

2016A

A

8

227

2016A

A

9

239

2016A

A

10

247

2016A

A

11

197

2016A

A

12

253

2016A

C

1

1141

2016A

C

2

998

2016A

C

3

887

2016A

C

4

916

2016A

C

5

660

2016A

C

6

651

2016A

C

7

687

2016A

C

8

552

2016A

C

9

698

2016A

C

10

600

2016A

C

11

616

2016A

C

12

1195

    

 

‘B’ Summary for 1 Item

ItemNumber

CompanyCode

StDev.P

2016A

A

35.68

2016A

B

7.35

2016A

C

210.73

  

253.76

 

Now when I drag CompanyCode from (2.Method ‘B’) and try to filter I can filter it in proper way, but when nothing is selected the standard deviation should be calculated as ‘208.55’ which is correct from (1.Method ‘B’), But I am getting ‘253.76’ which is the total of the three standard deviations.

What should I do to get standard deviation to be 208.55 when I don’t select any Company?

My requirement is to filter on the results, if I use 1.Method then I am unable to filter on companies even though there is join between Fact and DimCompany.

What did I miss?

am I not doing it in write way?

Please help me out with this.!!

Thanks in advance.

 

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.

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    anybody there..??

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Anonymous

       

      Let me go through this and get back to you at the earliest.

       

      CheenuSing

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        hi Anonymous,

         

        did you get any..??

         

        Thanks in advance.

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello all together,  

    i habe a Problem with Ma Chart as you can see below.

    The average is Not exactly on  top of the Distribution. 
    What do you think is the Problem?

    Thanks in advance