Stdevp Of Dimension totals as reference line in bar graph, ignoring sub-dimensions

[PedroH]

'Morning!

Stdevp as reference line was used to simulate the behavior

The goal is the following:
To have a bar chart with the distinct count of patient IDs, by month (x axis), by hospital (bar colors). The bar chart should display a reference line with the stdevp of distinct count by month, ignoring the subdimension.
This is a sample of the dataset:

And this is the indended behavior:

The problem has been that I can’t figure out a way to calculate the value ignoring the ‘Hospital’ dimension.

With the formula

[Reference Line Value]
stdevpOver(distinct_count({Patient ID}), [])

I get the following behavior. In the third table the values change, because now the sample size is bigger (since as a table calculation it will consider all ‘sub-values’ as well).

And that is also reflected when trying to plot the values. By creating reference lines with max, min and average we can see there are multiple values, which doesn’t happen with the first one.

I have already tried a mix of combinations of LAC-As and -Ws, and corresponding dimensions, but still can’t figure out a way to make it work.

[DylanM]

Hello @PedroH, I think I understand the solution you are looking for. I spent some time playing with the calculated fields and I was able to create one that got me close to your expected output.

Here is the link to the arena dashboard I created Stdevp Of Dimension Totals

Let me know if this is returning the values you are expecting. Thank you!

[DylanM]

Hello @PedroH, since we have not heard back from you, I will mark my last response as the solution. Please let me know if you have any remaining questions and I can guide you further. Thank you!