0

I'm trying to compute the standard deviation of a nested calculated measure.

In this example, different countries produce a number of items every month, each with a specific colour. I'm trying to sort countries by the standard deviation of the monthly ratio between warm and cold colours for every country.

The underlying data is as follows (each row is an item produced at a certain date by a certain country):

date         country    colour
-------------------------------
2020-03-01   France     Blue
2020-03-01   UK         Red
2020-03-02   USA        Green
2020-03-03   Belgium    Red
2020-03-04   UK         Green

The first calculated measures identifies all the items which are either warm or cold colours:

WARM_COLOUR:
{INCLUDE [Colour]: SUM(If [Colour] = 'Red' or [Colour] = 'Orange' or [Colour] = 'Yellow' THEN 1 ELSE 0 END)}

COLD_COLOUR:
{INCLUDE [Colour]: SUM(If [Colour] = 'Blue' or [Colour] = 'Green' THEN 1 ELSE 0 END)}

Then, I compute the ratio between warm and cold colours:

WARM_COLD_RATIO
sum([WARM_COLOR]) / (sum([WARM_COLOUR]) + SUM([COLD_COLOUR]))

Finally, I want to compute, for every country, the standard deviation of this ratio, but this produces an error:

{INCLUDE [Country]: STDEV([WARM_COLD_RATIO])}
                    ^^^^^ Error: argument to STDEV is already an aggregation and can't be aggregated further

The final desired result is that I want to sort countries by descending order of standard deviation of the warm/cold colours ratio, per time period (e.g. month). Specifically, a country for which the warm/cold ratio would vary a lot every month, would come on top, whereas a country which gets the same warm/cold ratio every month would come last.

Jivan
  • 21,522
  • 15
  • 80
  • 131

1 Answers1

0

Table calculations can't be inside LOD calculations.

Any reason it really needs to be a LOD? Are there good table calculation alternative formulas, such as WINDOW_STDEV?

WINDOW_STDEV([WARM_COLD_RATIO])
Andy TAR Sols
  • 1,725
  • 2
  • 5
  • 12