Calculating Standard Dev from an aggregate or measure

[Anonymous]

Hi Guys

I have spent over past 2 days trying to figure out a solution to this problem, i have been asked to create a SPC by a client, in which the main table is in this a raw format with multiple rows i.e customer name, day, number of sales etc

C TABLE

Customer name, day, number of sales

A                       1               10

B                      1               10

C                      1                20

A                      2                10

B                      2                 40

So i have managed to create an aggregate table using mixture of different measures which looks like this with day as filter

Customer name        Number of sales            Mean             My ideal answer

A                              20                                  30                   13.33

B                               50                                  30                   13.33

C                               20                                 30                    13.33

                                  90                                30

Mean is calculated using measure

M Measure

Total number of sales = SUM(C[Number of Sales])

DISTINCT COUNT of CUS = DISTINCTCOUNT(C[Customer name])

Mean = CALCULATE (DIVIDE(M[Total Number of Sales], M[DISTINCT COUNT of CUS], ALLEXCEPT(C, C[Day]))

I have tried to apply the similar logic to work out Standard Derivation using STDV.P against C[Number of sales], but realised that the STDV.P is calculating the derviation of the C table and not the aggregate table. 

So next i have tried using the same approach with SQRT((Total Sales - Mean)^2) - but realised that the result showing as

A       SQRT( (20-30)^2)

B        SQRT( (50-30)^2)

C        SQRT( (20-30)^2)

          SQRT( (90-30)^2) <<<< which is wrong it should be average of all above

My ideal answer for all rows for the aggregate table should be 

AVERAGE( SQRT( (20-30)^2) + SQRT( (50-30)^2) +  SQRT( (20-30)^2)) which is STDV.P on my aggregate NOT my table

I am getting to stage of giving up and would be grateful if any guru or master can help!!!!!!!!!!!!!!!!!!   

Happy to share the file 

[Anonymous · Accepted solution]

update**

Managed to solve the problem by using

3 seperate measure:

1. calculate the sum of values (using SUM( No of Sales ) )

2. Using STDEVX.P( C Table, SUMMARISE(Customer Name, CALCULATE ( 1st Measure ))

3. CALCULATE( 2nd Measure, ALLSELECTED(Customer Name)

Hopefully this will help others with the similar problem

[v-cherch-msft]

Hi @Anonymous 

You may try below measure:

answer =
VAR a =
    SUMMARIZE (
        ALLEXCEPT ( C, C[day] ),
        C[Customer name],
        "b", SQRT ( ( [Total number of sales] - [Mean] ) ^ 2 )
    )
RETURN
    AVERAGEX ( a, [b] )

Regards,

Cherie

[Anonymous]

thanks for your reply - will have a look and get back to you.

Much appreciated !!

[v-cherch-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.

Regards,

Cherie