Dinamic Correlation

[Monica]

Hi!

I've been trying to do what said in this post http://community.powerbi.com/t5/Community-Blog/DAX-as-a-Language/ba-p/22120 with my own data, the problem is that I did all process and I have the correlation but I need that if I do some filter the correlation has to be re calculate, it's because for example I need to find the correlation in different cities. So I changed your code:

//First X

CorrelateX3 = 
VAR AverageX3= AVERAGEX(ALLSELECTED('Llamadas Une'),[Valor])
RETURN	([Valor]-AverageX3)

//Then Y

CorrelateY3 = 
VAR 
	AverageY3 = AVERAGEX(ALLSELECTED('TRPS Une Hogares'),[Trps P Mas 25 MA])
RETURN
	 ([Trps P Mas 25 MA]- AverageY3)

//Finally the corelation

Correlation2 = 
VAR SumCorrelateX3SumCorrelateX3= SUMX(ALLSELECTED('Llamadas Une'),[CorrelateX3]*[CorrelateX3])
VAR SumCorrelateX3SumCorrelateY3= SUMX(ALLSELECTED('Llamadas Une'),[CorrelateX3]*RELATED('TRPS Une Hogares'[CorrelateY3]))
VAR SumCorrelateY3CorrelateY3= SUMX(ALLSELECTED('Llamadas Une'),RELATED('TRPS Une Hogares'[CorrelateY3])*RELATED('TRPS Une Hogares'[CorrelateY3]))
RETURN	(
SumCorrelateX3SumCorrelateY3 / SQRT(SumCorrelateX3SumCorrelateX3*SumCorrelateY3CorrelateY3))

I calculated the x average to testing:

//no filter
AverageX = AVERAGEX(ALL('Llamadas Une'),[Valor])

//Filter, it is like AverageX3

Average lla = AVERAGEX(ALLSELECTED('Llamadas Une'),[Valor])

I found that the error is because the subtraction between [Valor]-AverageX3 it's not ok:

As you can see here, the result in CorrelateX3 is equal to subtract Valor-AverageX and not Valor-AverageX3. I don't know why it's happend here, Colud you Help me with this issue? or do you have other idea to do a dinamic correlation?

Thanks

[Monica]

Hi!

I've been trying to do what said in this post http://community.powerbi.com/t5/Community-Blog/DAX-as-a-Language/ba-p/22120 with my own data, the problem is that I did all process and I have the correlation but I need that if I do some filter the correlation has to be re calculate, it's because for example I need to find the correlation in different cities. So I changed your code:

//First X

CorrelateX3 = 
VAR AverageX3= AVERAGEX(ALLSELECTED('Llamadas Une'),[Valor])
RETURN	([Valor]-AverageX3)

//Then Y

CorrelateY3 = 
VAR 
	AverageY3 = AVERAGEX(ALLSELECTED('TRPS Une Hogares'),[Trps P Mas 25 MA])
RETURN
	 ([Trps P Mas 25 MA]- AverageY3)

//Finally the corelation

Correlation2 = 
VAR SumCorrelateX3SumCorrelateX3= SUMX(ALLSELECTED('Llamadas Une'),[CorrelateX3]*[CorrelateX3])
VAR SumCorrelateX3SumCorrelateY3= SUMX(ALLSELECTED('Llamadas Une'),[CorrelateX3]*RELATED('TRPS Une Hogares'[CorrelateY3]))
VAR SumCorrelateY3CorrelateY3= SUMX(ALLSELECTED('Llamadas Une'),RELATED('TRPS Une Hogares'[CorrelateY3])*RELATED('TRPS Une Hogares'[CorrelateY3]))
RETURN	(
SumCorrelateX3SumCorrelateY3 / SQRT(SumCorrelateX3SumCorrelateX3*SumCorrelateY3CorrelateY3))

I calculated the x average to testing:

//no filter
AverageX = AVERAGEX(ALL('Llamadas Une'),[Valor])

//Filter, it is like AverageX3

Average lla = AVERAGEX(ALLSELECTED('Llamadas Une'),[Valor])

I found that the error is because the subtraction between [Valor]-AverageX3 it's not ok:

As you can see here, the result in CorrelateX3 is equal to subtract Valor-AverageX and not Valor-AverageX3. I don't know why it's happend here, Colud you Help me with this issue? or do you have other idea to do a dinamic correlation?

Thanks