Forum Discussion
STDEV.S function in DAX to target specific cell range
Hi Everyone,
I badly need help to this, I've been searching for a solution in this community for days but still not successful.
I have a table in excel that I wanted to create as a calculated table using dax. I've already completed creating the date table, and other columns but I am having challenges to to use STDEV.S function to target on a specific cell range in my data table.
See the excel table and formula used in excel in the screenshot below.
Thanks in advance
| Month Year | Column 1 | Column 2 | Std Dev | Total |
| Jul-16 | 0 | 0 | 0 | 0 |
| Aug-16 | 0 | 0 | 0 | 0 |
| Sep-16 | 0 | 0 | 0 | 0 |
| Oct-16 | 0 | 0 | 0 | 0 |
| Nov-16 | 0 | 0 | 0 | 0 |
| Dec-16 | 0 | 0 | 0 | 0 |
| Jan-17 | 3 | 1 | 1.511857892 | 4 |
| Feb-17 | 4 | 2 | 2.375469878 | 6 |
| Mar-17 | 5 | 3 | 3.16227766 | 8 |
| Apr-17 | 6 | 4 | 3.91010088 | 10 |
| May-17 | 7 | 5 | 4.631905165 | 12 |
| Jun-17 | 8 | 6 | 5.334280219 | 14 |
| Jul-17 | 9 | 7 | 6.021329609 | 16 |
| Aug-17 | 10 | 8 | 6.71011483 | 18 |
| Sep-17 | 11 | 9 | 7.29594197 | 20 |
| Std Dev | Total |
| 0 | =C2+D2 |
| =STDEV.S(F2:F3) | =C3+D3 |
| =STDEV.S(F2:F4) | =C4+D4 |
| =STDEV.S(F2:F5) | =C5+D5 |
| =STDEV.S(F2:F6) | =C6+D6 |
| =STDEV.S(F2:F7) | =C7+D7 |
| =STDEV.S(F2:F8) | =C8+D8 |
| =STDEV.S(F2:F9) | =C9+D9 |
| =STDEV.S(F2:F10) | =C10+D10 |
| =STDEV.S(F2:F11) | =C11+D11 |
| =STDEV.S(F2:F12) | =C12+D12 |
| =STDEV.S(F2:F13) | =C13+D13 |
| =STDEV.S(F2:F14) | =C14+D14 |
| =STDEV.S(F3:F15) | =C15+D15 |
| =STDEV.S(F4:F16) | =C16+D16 |
Hi Anonymous ,
Please try to use below calculated column formulas if they suitable for your requirement:
Total = [Column 1]+[Column 2] STDEV.S = VAR curr = [Month Year] RETURN STDEVX.S ( FILTER ( ALL ( T5 ), [Month Year] >= DATE ( YEAR ( curr ) - 1, MONTH ( curr ), DAY ( curr ) ) && [Month Year] <= curr ), [Total] )Regards,
Xiaoxin Sheng
5 Replies
Thanks for the info Anonymous .
Already created an index using the formula below as my table is created using dax:
Index = CALCULATE(COUNTROWS('CR Trend'), FILTER(ALL('CR Trend'), 'CR Trend'[Year-MonthName].[Date]<= EARLIER('CR Trend'[Year-MonthName].[Date])))However tried creating some conditions but it's not working. I don't know what functions to be use etc as I am new to DAX functions.
Any suggestion how to build that condition is very much appreaciated.
Hi,
I badly needed help in this one as I haven't found any solution yet.
I have a calculated column and wanted to create a STDEV.S column using dax to calculate the first 13 cell then calculate 1 cell down (next 13) and so on.
If you'll look in an excel table, it is similar to below.
Index Month Year Column 1 Column 2 Std Dev Total 1 Jul-16 0 0 0 0 2 Aug-16 0 0 0 0 3 Sep-16 0 0 0 0 4 Oct-16 0 0 0 0 5 Nov-16 0 0 0 0 6 Dec-16 0 0 0 0 7 Jan-17 3 1 1.511857892 4 8 Feb-17 4 2 2.375469878 6 9 Mar-17 5 3 3.16227766 8 10 Apr-17 6 4 3.91010088 10 11 May-17 7 5 4.631905165 12 12 Jun-17 8 6 5.334280219 14 13 Jul-17 9 7 6.021329609 16 14 Aug-17 10 8 6.71011483 18 15 Sep-17 11 9 7.29594197 20 Dev Total 0 =C2+D2 =STDEV.S(F2:F3) =C3+D3 =STDEV.S(F2:F4) =C4+D4 =STDEV.S(F2:F5) =C5+D5 =STDEV.S(F2:F6) =C6+D6 =STDEV.S(F2:F7) =C7+D7 =STDEV.S(F2:F8) =C8+D8 =STDEV.S(F2:F9) =C9+D9 =STDEV.S(F2:F10) =C10+D10 =STDEV.S(F2:F11) =C11+D11 =STDEV.S(F2:F12) =C12+D12 =STDEV.S(F2:F13) =C13+D13 =STDEV.S(F2:F14) =C14+D14 =STDEV.S(F3:F15) =C15+D15 =STDEV.S(F4:F16) =C16+D16 Hi Anonymous ,
Please try to use below calculated column formulas if they suitable for your requirement:
Total = [Column 1]+[Column 2] STDEV.S = VAR curr = [Month Year] RETURN STDEVX.S ( FILTER ( ALL ( T5 ), [Month Year] >= DATE ( YEAR ( curr ) - 1, MONTH ( curr ), DAY ( curr ) ) && [Month Year] <= curr ), [Total] )Regards,
Xiaoxin Sheng