cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Garett52 Frequent Visitor
Frequent Visitor

Incorrect Standard Deviation of a measure

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






1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Incorrect Standard Deviation of a measure

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
    )
)

Burn Diff COl.png

 

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

 

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

Final Result:

Final Result.png

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

9 REPLIES 9
Super User
Super User

Re: Incorrect Standard Deviation of a measure

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 Frequent Visitor
Frequent Visitor

Re: Incorrect Standard Deviation of a measure

Hi @Nick_M 

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 

Super User
Super User

Re: Incorrect Standard Deviation of a measure

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 Frequent Visitor
Frequent Visitor

Re: Incorrect Standard Deviation of a measure

 

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 

 

Capture.PNG

 

 

 

Super User
Super User

Re: Incorrect Standard Deviation of a measure

can you upload the pbix file?

Garett52 Frequent Visitor
Frequent Visitor

Re: Incorrect Standard Deviation of a measure

Garett52 Frequent Visitor
Frequent Visitor

Re: Incorrect Standard Deviation of a measure

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

 

Super User
Super User

Re: Incorrect Standard Deviation of a measure

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
    )
)

Burn Diff COl.png

 

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

 

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

Final Result:

Final Result.png

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 Frequent Visitor
Frequent Visitor

Re: Incorrect Standard Deviation of a measure

@Nick_M 

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 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 6 members 3,403 guests
Please welcome our newest community members: