CODEWITHSUNDEEP
tableau-apiweighted
Randy Adhitama
Randy Adhitama

Reputation: 227

Tableau, how to calculate Weighted Standard Deviation

I've a problem to calculate weighted standard deviation. Here's the formula I used:

sum([Weight]*(([Variable]-[Mean Score - Variable])^2))
/
SUM([Weight])

But there's a error pop up message "Cannot mix aggregrate and non-aggregrate"

I wonder what's wrong with my formula?

Thanks

Upvotes: 1

Views: 1814

Answers (1)

Alex Blakemore
Alex Blakemore

Reputation: 11921

I am assuming Variable and Weight are explicit fields in your dataset, while [Mean Score] is a calculated field you defined in Tableau.

[Mean Score] is an aggregate calculation; Variable is not. You can check this by dragging [Mean Score] to any shelf in Tableau, and note that it is show within the prefix AGG(). Note that you can't select the form of aggregation (SUM, MIN, AVG) to apply in that case, because the aggregation function is defined within that calculation.

You can't mix aggregate and record level calculations directly. Record level calculations are evaluated once for each individual data row. Aggregate calculations are evaluated once for each block of data rows.

The dimensions used in your worksheet determine which data rows are grouped together into blocks (partitioning the data). Analogous to the fields that follow the keyword GROUP BY in SQL select statement. As with SQL, the other fields referenced must be aggregated somehow such as via a SUM(), MIN(), MAX() or other call. Tableau calls those fields measures.

The most straightforward solution is to revise your definition of [Mean Score] to make it a Level Of Detail (LOD) calc instead of an aggregate calc.

That will allow you to essentially first compute the mean score separately, and then reference that result in your record level calculation. You will have to decide among 3 different ways for determining the dimensions for your LOD calc. See the online help for more info on LOD calcs.

For example, try replacing [Mean Score] with { include : [Mean Score] }

Upvotes: 1

Related Questions