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
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
Solved! Go to 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:
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
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
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
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:
@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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
98 | |
96 | |
87 | |
68 |
User | Count |
---|---|
169 | |
133 | |
130 | |
103 | |
95 |