Forum Discussion
Calculate measure (standard deviation) based on selection
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
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
6 Replies
Can you share the pbix file with sample data?
Hi Gopal30,
thank you for your fast response. Please find the file here: https://ufile.io/eojbwrjt
Best regards,
Tom
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 worksHi 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
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
