Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Garett52
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
Anonymous
Not applicable

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

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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

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
Not applicable

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

 

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

 

 

 

Anonymous
Not applicable

can you upload the pbix file?

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
Not applicable

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

@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 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.