Incorrect Standard Deviation of a measure

[Garett52]

Hello

I'm having trouble getting the correct standard deviation of a difference measure in the "N" numbers. I'm using direct query with a page level filter of N<0.03 

What I would like is a card visual to display the standard deviation of the differences over a date range by equipment name. STDEVX.P/S in Power BI is giving a much higher value than Excel. 

Thanks

Difference = IF(MIN(TEST_RESULT[BURN_NO])=1, CALCULATE(AVERAGE(TEST_RESULT[N]),TEST_RESULT[BURN_NO] = 1) - CALCULATE(AVERAGE(TEST_RESULT[N]), TEST_RESULT[BURN_NO] = 2), CALCULATE(AVERAGE(TEST_RESULT[N]),TEST_RESULT[BURN_NO] = 2) - CALCULATE(AVERAGE(TEST_RESULT[N]), TEST_RESULT[BURN_NO] = 3))

Sample ID BURN_NO Equip Date N
1206A1 1 BSB4 2019-Jul-10 10:04:47 0.00559
1206A1 2 BSB4 2019-Jul-10 10:04:46 0.00579
1206A2 1 BSB5 2019-Jul-10 12:21:38 0.00584
1206A2 2 BSB5 2019-Jul-10 12:21:37 0.00584
1206A3 1 BSB4 2019-Jul-10 00:38:43 0.00559
1206A3 2 BSB4 2019-Jul-10 00:38:42 0.00563
1207A1 1 BSB3 2019-Jul-10 08:06:14 0.00604
1207A1 2 BSB3 2019-Jul-10 08:06:14 0.00649
1207A2 1 BSB5 2019-Jul-10 01:03:53 0.00706
1207A2 2 BSB5 2019-Jul-10 01:03:52 0.00712
1207A2 3 BSB5 2019-Jul-10 01:03:51 0.00846
1207A2 4 BSB5 2019-Jul-10 01:03:50 0.012
1207A3 1 BSB4 2019-Jul-10 01:22:20 0.00752
1207A3 2 BSB4 2019-Jul-10 01:22:20 0.00673
1207A3 3 BSB4 2019-Jul-10 01:22:18 0.00863
1207A3 4 BSB4 2019-Jul-10 01:22:18 0.00715
1207B1 1 BSB5 2019-Jul-10 00:10:17 0.00652
1207B1 2 BSB5 2019-Jul-10 00:10:17 0.00669
1208A1 1 BSB3 2019-Jul-10 00:29:48 0.00651
1208A1 2 BSB3 2019-Jul-10 00:29:47 0.00669
1208A2 1 BSB4 2019-Jul-10 01:32:20 0.00586
1208A2 2 BSB4 2019-Jul-10 01:32:19 0.00868
1208A2 3 BSB4 2019-Jul-10 01:32:19 0.00638
1208A3 1 BSB3 2019-Jul-10 02:03:54 0.00549
1208A3 2 BSB3 2019-Jul-10 02:03:54 0.00519
1209B1 1 BSB5 2019-Jul-10 05:15:01 0.00523
1209B1 2 BSB5 2019-Jul-10 05:15:00 0.00537
1209B2 1 BSB3 2019-Jul-10 05:29:55 0.00572
1209B2 2 BSB3 2019-Jul-10 05:29:53 0.00554
1210A1 1 BSB4 2019-Jul-10 06:52:51 0.00641
1210A1 2 BSB4 2019-Jul-10 06:52:51 0.00679
1210A2 1 BSB5 2019-Jul-10 07:10:31 0.006
1210A2 2 BSB5 2019-Jul-10 07:10:30 0.00585
1210A3 1 BSB4 2019-Jul-10 07:27:55 0.00622
1210A3 2 BSB4 2019-Jul-10 07:27:54 0.00571

[Anonymous]

This should give you a start.  From there you can add in your filteres for dates selected and what not:

Measure = 
CALCULATE( 
    STDEVX.P( Table1,  Table1[N] ),
    FILTER(
        Table1,
        Table1[N] <0.03
    )
)

[Garett52]

Hi @Anonymous 

That's what I was using already and the results are roughly 10x or more than the same calculation in Excel. Also, I need the standard deviation of the difference in N values for each equipment.

Thanks

Garett 

[Anonymous]

any chance you could post an example of what you would like the outcome to be? Having a hard time seeing what you are describing

[Garett52]

In the capture, Measure is using STDEV.S in Excel which gives the correct result. Measure 2 is using STDEVX.S of the Burn_Difference and is much higher. What I'm trying to do is get the standard deviation of the Burn_Difference to match what Excel gives but I'm not sure where the issue is.

Thanks 

[Anonymous]

can you upload the pbix file?

[Garett52]

PBIX for anyone interested.

 https://1drv.ms/u/s!AjZC4oN-YmepgScROKKFeay8UitD?e=b7RTTY

[Garett52]

So after more testing it seems that my DAX is not behaving as I expected and is subtracting all Burn 2 N results from 0. I get the expected result as it is graphed by Sample ID but this is where the Standard Deviation issue arises from. 

How can I get a measure to only subtract N values (Burn 1 - Burn 2) if the Sample ID is the same? 

Thanks

[Anonymous · Accepted solution]

Please take a look at the attached. I put the Burn Differences in a calculated column using the following:

( I also broke out some of the data into Dimension tables to make it easier)

Burn Diff = 
var __CurrentDate= FactTable[Date]
var __CurremtTime= FactTable[Time]
var __CurrentBurn= FactTable[BURN_NO]
Var __CurrentSampleID= FactTable[SampleIDKey]
return


CALCULATE(
    sum( FactTable[N]),
    Filter( 
        FactTable,
        FactTable[Date] >= __CurrentDate
        &&
        FactTable[Time] >= __CurremtTime
        &&
        FactTable[BURN_NO] = __CurrentBurn - 1
        &&
        FactTable[SampleIDKey] =__CurrentSampleID
        &&
        FactTable[BURN_NO] =1
    )
)
-
CALCULATE(
    sum( FactTable[N]),
    Filter( 
        FactTable,
        FactTable[Date] >= __CurrentDate
        &&
        FactTable[Time] >= __CurremtTime
        &&
        FactTable[BURN_NO] = __CurrentBurn 
        &&
        FactTable[SampleIDKey] =__CurrentSampleID
        && FactTable[BURN_NO] =2
    )
)

Then from there we can just use a simple standard dev function:

Std Dev = STDEV.s(FactTable[Burn Diff])

Final Result:

Now, doing this as a calculated column doesnt make it crazy flexible, so we could write it all using measures, but i figured this would be a good place to start out at 

Here's the pbix file:

https://1drv.ms/u/s!Amqd8ArUSwDS3TaQuLzd1YPZF4w_?e=fc0Pai

[Garett52]

@Anonymous 

This is great! But I have one more problem. 

When I use it on real data and there is no data for Burn 1 it is currently subtracting from zero. Is there a way to make this get the difference (Burn 2 - Burn 3) if no value for Burn 1? 

Thanks