Calculate measure (standard deviation) based on selection

[Anonymous]

Hi power bi community,

currently I struggle with calculating a measure, based on the filter selection. My data looks like the following:

The complete power bi app can be found here.

I want to calculate the standard deviation of the revenue. In my case I want to know the standard deviation of every single country over all selected months.

Of course, dependend on what the user selects, the standard deviation has to be recalculated. The user might for example select only certain articles.

What I understand is, when using the available function stdev.p, the calculation is done not on the displayed and summed up values, but on every single row in the table which holds the data in the background. 

I also tried everything with the stdevx.p function together with the allexcept function. As you can see the standard deviation is not calculated based on the values in the revenue column. The expected result is e.g. for Austria 314,22. As an example I calculated that in excel:

Because of that "limitation" i tryed to calculate the standard deviation on my own with measures step by step. Meanwhile I'am sure, that this is nothing which is good and afterwards also maintainable. So, can please somebody support us in solving that problem? I'am sure there is a much better way or even just a simple solution for that.

Thank you very much in advance for you support.

Best regards,

Tom

[Gopal30]

Meanwhile you can try the following formula as "Calculated Column" (Not Measure):

STDEV1 = CALCULATE(STDEV.S('Tabelle1'[Revenue]), FILTER('Tabelle1',('Tabelle1'[Country])=EARLIER('Tabelle1'[Country])),FILTER('Tabelle1',(Tabelle1[Distinct Monat]) = EARLIER('Tabelle1'[Distinct Monat])))

Check if it works

[Anonymous]

Hi Gopal30,

meanwhile I tryed your proposal. Unfortunately I had no success.

Here is what I did (it is a column - no measure):

Any other suggestion what I can do? I mean, it is a general problem for me to calculate something based on what there is shown in the power bi matrix. Average is another example where I want to calculate column total divided by number of rows within the matrix and show this in every row.

Anyway, thank you very much for your reply.

Best regards,

Tom

[TeigeGao · Accepted solution]

Hi @Anonymous ,

>>I mean, it is a general problem for me to calculate something based on what there is shown in the power bi matrix.

In this scenario, we will need to use the measure rather than a calculated column, a calculated column will be calculated on whole data, the filter will not change it.

We can create a measure using the following DAX query:

STDEV1 = CALCULATE(STDEV.P(Tabelle1[Revenue]),FILTER(ALL(Tabelle1),Tabelle1[Country] = MIN(Tabelle1[Country]) && Tabelle1[Distinct Monat] in ALLSELECTED(Tabelle1[Distinct Monat])))

The result will like below: 

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao,

checking this post, I think have similar issue when it comes to add a standard deviation as a measure.

I have a pivot table with data coming from my data model, showing following data:

- Week numbers in columns [weeknum]

- Product ID in rows [Producto]

- Quantity consumed as Σ [Cantidad]

Point is, a lot of products have zero consumption along the year.

Here comes a screenshoot (week numbers reduced for pasting smaller picture):

What I want is to add a measure showing the total standard deviation as a column at the end based on column selection (eventually will be 52 weeks).

Can you advise?

Thanks,

[Gopal30]

Can you share the pbix file with sample data? 

[Anonymous]

Hi Gopal30,

thank you for your fast response. Please find the file here: https://ufile.io/eojbwrjt

Best regards,

Tom