Forum Discussion
Calculating a Weighted Standard Deviation
Greeting!
I started implementing POWER BI (Which is awesome). However, I am struggling to get my head around DAX, especially when trying to calculate an overall "weighted standard deviation". I know this would be an easy one for many.
Here is an example of the issue I am coming across.
Thanks
Hi amitchandak ,thanks for your quick reply, I will add more.
Hi KDGRF ,
'Total' is calculated after removing the filter conditions.
The Table data is shown below:
The filter condition in the 'Table' visual is the column 'Product'. The result of the total is consistent with the value in the card visual.(There are no filters for either)
Please follow these steps:
1.Use the following DAX expression to create a table
Table 2 = VAR _table1 = SUMMARIZE('Table','Table'[Product], "Count of Strength",COUNT('Table'[Strength]), "Cal.1",IF( COUNT('Table'[Strength]) > 3,STDEV.P('Table'[Strength]),BLANK()), "Cal.2",IF(COUNT('Table'[Strength] ) > 3,COUNT('Table'[Strength]),BLANK())) VAR _table2 = ADDCOLUMNS(_table1,"Cal.3",[Cal.1] * [Cal.2]) RETURN _table2Modify it according to your needs.
2.Use the following DAX expression to create measures.(Use the function 'SELECTEDVALUE' to determine whether the row is 'Total', if so, then 'SELECTEDVALUE' returns null.)
SELECTEDVALUE function - DAX | Microsoft Learn
Count of Strength = IF(ISBLANK(SELECTEDVALUE('Table'[Product])), SUM('Table 2'[Count of Strength]), COUNT('Table'[Strength]))Cal.1 = IF(ISBLANK(SELECTEDVALUE('Table'[Product])), SUM('Table 2'[Cal.1]), IF(COUNT('Table'[Strength]) > 3,STDEV.P('Table'[Strength]),""))Cal.2 = IF(ISBLANK(SELECTEDVALUE('Table'[Product])), SUM('Table 2'[Cal.2]), IF(COUNT('Table'[Strength] ) > 3,COUNT('Table'[Strength]),""))Cal.3 = IF(ISBLANK(SELECTEDVALUE('Table'[Product])), SUM('Table 2'[Cal.3]), IF([Cal.1] = "","",[Cal.1] * [Cal.2]))3.Final output
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
KDGRF , Please refer if blogs from SQLBI
https://www.sqlbi.com/tv/stdev-p-stdev-s-stdevx-p-stdevx-s-dax-guide/Or you can try measures like
Weighted Mean =
DIVIDE(
SUMX('Table', 'Table'[Value] * 'Table'[Weight]),
SUM('Table'[Weight])
)
Weighted Variance =
DIVIDE(
SUMX(
'Table',
'Table'[Weight] * ('Table'[Value] - [Weighted Mean])^2
),
SUM('Table'[Weight])
)
Weighted SD =
SQRT([Weighted Variance])Hi Amit,
Thanks for your response.
Unfortunately, it did not work.
I have been able to calculate the condition and weight. As you can see below. However, the totals are incorrect, and when I create the final measure (weight / number of samples), the Standard deviation remains the same and incorrect.Hi amitchandak ,thanks for your quick reply, I will add more.
Hi KDGRF ,
'Total' is calculated after removing the filter conditions.
The Table data is shown below:
The filter condition in the 'Table' visual is the column 'Product'. The result of the total is consistent with the value in the card visual.(There are no filters for either)
Please follow these steps:
1.Use the following DAX expression to create a table
Table 2 = VAR _table1 = SUMMARIZE('Table','Table'[Product], "Count of Strength",COUNT('Table'[Strength]), "Cal.1",IF( COUNT('Table'[Strength]) > 3,STDEV.P('Table'[Strength]),BLANK()), "Cal.2",IF(COUNT('Table'[Strength] ) > 3,COUNT('Table'[Strength]),BLANK())) VAR _table2 = ADDCOLUMNS(_table1,"Cal.3",[Cal.1] * [Cal.2]) RETURN _table2Modify it according to your needs.
2.Use the following DAX expression to create measures.(Use the function 'SELECTEDVALUE' to determine whether the row is 'Total', if so, then 'SELECTEDVALUE' returns null.)
SELECTEDVALUE function - DAX | Microsoft Learn
Count of Strength = IF(ISBLANK(SELECTEDVALUE('Table'[Product])), SUM('Table 2'[Count of Strength]), COUNT('Table'[Strength]))Cal.1 = IF(ISBLANK(SELECTEDVALUE('Table'[Product])), SUM('Table 2'[Cal.1]), IF(COUNT('Table'[Strength]) > 3,STDEV.P('Table'[Strength]),""))Cal.2 = IF(ISBLANK(SELECTEDVALUE('Table'[Product])), SUM('Table 2'[Cal.2]), IF(COUNT('Table'[Strength] ) > 3,COUNT('Table'[Strength]),""))Cal.3 = IF(ISBLANK(SELECTEDVALUE('Table'[Product])), SUM('Table 2'[Cal.3]), IF([Cal.1] = "","",[Cal.1] * [Cal.2]))3.Final output
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
