Standard deviation for categories

Question

Hi I have desperately been trying to work this out and have referred to several posts but am still not getting the correct answer!

I have a bunch of providers of different provider type. I calculate an average cost change for each provider (from more granular payment data). I then want to find the standard deviation of these provider level changes for the difference provider type.

This is where I've got up to with the dax - this gives the same standard deviation across all provider types rather than the required output.

group_test = 
var tab1 = SUMMARIZECOLUMNS(ProvData[Provider Type],ProvData[Provider Code], "prov_avg",AVERAGEX(core_data, sum(PayData[Payment1])-sum(PayData[Payment2]))/SUM(PayData[Payment1]))    
var sd_type = SELECTCOLUMNS(SUMMARIZE(tab1,[Provider Type],[Provider Code], "test", STDEVX.S(tab1,[prov_avg])), "sd_type", [test])
var tab2 =  ADDCOLUMNS(tab1, "sd_type", sd_type)
return tab2

I want my final table to look like this

Provider Code	Provider type	Prov_avg	sd_type
1	a	x	sd for a
2	a	y	sd for a
3	b	z	sd for b

Thanks in advance for any help

Answer 1

Add a column to your table:

stdColumn = 
var prov_Code = ProvData[Provider Code]
var prov_type = ProvData[Provider Type]
var stdValue = CALCULATE (STDEV.S([prov_avg]), FILTER(prov_Code = ProvData[Provider Code] && prov_type = ProvData[Provider Type]))
return stdValue

So what we do is to calculate the stdev based on the filter given on Code & Type