Standard Deviation

[taumirza]

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.

[Tomclark23]

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 

 

[Eric_Zhang]

@taumirza

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.

[taumirza]

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 · Accepted solution]

@taumirza

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]

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

[Eric_Zhang]

@taumirza

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.

[taumirza]

anybody there..??

[CheenuSing]

@taumirza

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

CheenuSing

[taumirza]

hi @CheenuSing,

did you get any..??

Thanks in advance.

[CheenuSing]

 @taumirza

Working on it give me some time.

Cheers

CheenuSing