How to write a Correlation Measure based on two different tables with variable Store Numbers

[lennox25]

I have two tables Sales and Hours. I've been asked to work out whats been worked out in Excel in Power Bi. 

Here is sample data for one store. Table 1 =  Sales Table 2  = Hours. They are not related. The correlation of these two tables data  = 0.62. Using the formula  = CORREL(B2:B19, B22:B39).   (B2:B19 in excel are all sold dates and B22:B39 are all the hours in that filter.

[smpa01]

@lennox25  when you are asking question in this forum always provide a sample data and expected output in a way that is easier for the responder to copy paste and provide a solution. You will experience an extreme fast repone in those case. There is no use of providing picture as no would go that far to do a dat entry first to provide an answer. SO please us to help you faster.

Sine there is no sample data/ pbix provided and there is no excel equivalent of `CORREL` exist in DAX, you need to handcraft the measure like this following

DEFINE 
	MEASURE sales [x] = SUMX(sales,sales[sales])
	MEASURE sales [y] = CALCULATE(SUM(hours[hours]),TREATAS(VALUES(sales[Date]),hours[Date]))
	MEASURE sales [XTimesY] = SUMX(all(sales),[sales]*[y])
	MEASURE sales [_sumX] = CALCULATE([x],ALL(sales))
	MEASURE sales [_sumY] = CALCULATE([y],ALL(sales))
	Measure sales [_count] = COUNTROWS(ALL(sales))
	MEASURE sales [_resultOne] = DIVIDE([_sumX]*[_sumY],[_count])
	MEASURE sales [numerator] = [XTimesY]-[_resultOne]
	MEASURE sales [sqX1] = SUMX(ALL(sales),sales[sales]^2)
	MEASURE sales [sqX2] = [_sumX]^2
	MEASURE sales [X3] = [sqX1]-DIVIDE([sqX2],[_count])
	MEASURE sales [sqY1] = SUMX(ALL(sales),[y]^2)
	MEASURE sales [sqY2] = [_sumY]^2
	MEASURE sales [Y3] = [sqY1]-DIVIDE([sqY2],[_count])
	MEASURE sales [X3TinesY3] = [X3]*[Y3]
	MEASURE sales [denomintor] = SQRT([X3TinesY3])
	MEASURE sales [COREL] = DIVIDE([numerator],[denomintor])
EVALUATE
    SUMMARIZECOLUMNS(sales[Date], 
	"x", [x],
	"y", [y],
	"XTimesY", [XTimesY],
	"_sumX", [_sumX],
	"_sumY",[_sumY],
	"_count", [_count],
	"_resultOne",[_resultOne],
	"numerator", [numerator],
	"sqx1", [sqX1],
	"sqX2", [sqX2],
	"X3",[X3],
	"sqY1", [sqY1],
	"sqY2", [sqY2],
	"Y3", [Y3],
	"X3TinesY3",[X3TinesY3],
	"denominator", [denomintor],
	"CORELATION", [COREL]
	)

I have assumed that you are performing this aggregation on sales table and sales and hours are not related

consistent with

[lennox25]

Hi @smpa01 

I tried using your measure in my report and it threw up numerous errors.

I attach a simple sample file. The result of the correlation should be one result = 0.62

https://drive.google.com/file/d/1-L3VRdlz9y9breLy6SDppWPdmLJ5WvZ4/view?usp=sharing 

[smpa01]

@lennox25  I am not sure what errors you are talking about as the pbix you shared does not show how you have tried the above and hence, no error. I would love to see the errors you are getting. Also, I can see that both the tables are connected through a bridging Date table which you did not mention previously. It changes few things as below.

DEFINE 

	MEASURE _measure[x] = SUMX(sales,sales[sales])
        /*CHANGE*/
	MEASURE _measure[y] = /*TREATAS is not required here as Date connects both sales and hours CALCULATE(SUM(hours[hours]),TREATAS(VALUES(sales[Date]),hours[Date]))*/ SUMX(hours,hours[hours])
	MEASURE _measure[XTimesY] = SUMX(all(sales),[sales]*[y])
	MEASURE _measure[_sumX] = CALCULATE([x],ALL(sales))
	MEASURE _measure[_sumY] = CALCULATE([y],ALL(sales))
        /*CHANGE*/
	Measure _measure[_count] = COUNTROWS(All('Date'))-COUNTROWS(FILTER(All('Date'),[x]-[y]==BLANK())) /*previously COUNTROWS(ALL(sales))*/
	MEASURE _measure[_resultOne] = DIVIDE([_sumX]*[_sumY],[_count])
	MEASURE _measure[numerator] = [XTimesY]-[_resultOne]
	MEASURE _measure[sqX1] = SUMX(ALL(sales),sales[sales]^2)
	MEASURE _measure[sqX2] = [_sumX]^2
	MEASURE _measure[X3] = [sqX1]-DIVIDE([sqX2],[_count])
	MEASURE _measure[sqY1] = SUMX(ALL(sales),[y]^2)
	MEASURE _measure[sqY2] = [_sumY]^2
	MEASURE _measure[Y3] = [sqY1]-DIVIDE([sqY2],[_count])
	MEASURE _measure[X3TinesY3] = [X3]*[Y3]
	MEASURE _measure[denomintor] = SQRT([X3TinesY3])
	MEASURE _measure[COREL] = DIVIDE([numerator],[denomintor])
	MEASURE _measure[t1] = "hello"
	MEASURE _measure[t2] = "world"
	
EVALUATE
    SUMMARIZECOLUMNS('Date'[Date], 
	"x", [x],
	"y", [y],
	"XTimesY", [XTimesY],
	"_sumX", [_sumX],
	"_sumY",[_sumY],
	"_count", [_count],
	"_resultOne",[_resultOne],
	"numerator", [numerator],
	"sqx1", [sqX1],
	"sqX2", [sqX2],
	"X3",[X3],
	"sqY1", [sqY1],
	"sqY2", [sqY2],
	"Y3", [Y3],
	"X3TinesY3",[X3TinesY3],
	"denominator", [denomintor],
	"CORELATION", [COREL]
	)

PFA file

[lennox25]

Hi @smpa01   - I tried this and it didnt work. I then added further sample data for several months and 4 stores and Im not sure why but there is a problem with the denominator measure. An argument of function 'SQRT' has the wrong data type or the result is too large or too small? Can you please help?

https://drive.google.com/file/d/1RqFHNVap4N8vF4RDtBrFb9RuxbKakJ4C/view?usp=sharing 

[smpa01 · Accepted solution]

@lennox25  can you try this

DEFINE
    MEASURE _measure[x] =
        SUMX ( _sales, _sales[sales] )
    MEASURE _measure[y] =
        SUMX ( _hours, _hours[hours] )
    MEASURE _measure[XTimesY] =
        SUMX ( ALL ( Datetable ), [x] * [y] )
    MEASURE _measure[_sumX] =
        CALCULATE (
            [x],
            FILTER ( ALL ( Datetable ), [x] <> BLANK () && [y] <> BLANK () )
        )
    MEASURE _measure[_sumY] =
        CALCULATE (
            [y],
            FILTER ( ALL ( Datetable ), [x] <> BLANK () && [y] <> BLANK () )
        )
    MEASURE _measure[_count] =
        COUNTROWS ( FILTER ( ALL ( Datetable ), [x] <> BLANK () && [y] <> BLANK () ) )
    MEASURE _measure[_resultOne] =
        DIVIDE ( [_sumX] * [_sumY], [_count] )
    MEASURE _measure[numerator] = [XTimesY] - [_resultOne]
    MEASURE _measure[sqX1] =
        SUMX (
            FILTER ( ALL ( Datetable ), [x] <> BLANK () && [y] <> BLANK () ),
            [x] ^ 2
        )
    MEASURE _measure[sqX2] = [_sumX] ^ 2
    MEASURE _measure[X3] =
        [sqX1] - DIVIDE ( [sqX2], [_count] )
    MEASURE _measure[sqY1] =
        SUMX (
            FILTER ( ALL ( Datetable ), [x] <> BLANK () && [y] <> BLANK () ),
            [y] ^ 2
        )
    MEASURE _measure[sqY2] = [_sumY] ^ 2
    MEASURE _measure[Y3] =
        [sqY1] - DIVIDE ( [sqY2], [_count] )
    MEASURE _measure[X3TinesY3] = [X3] * [Y3]
    MEASURE _measure[denomintor] =
        SQRT ( [X3TinesY3] )
    MEASURE _measure[COREL] =
        DIVIDE ( [numerator], [denomintor] )

EVALUATE
SUMMARIZECOLUMNS (
    'Datetable'[Date],
    "x", [x],
    "y", [y],
    "XTimesY", [XTimesY],
    "_sumX", [_sumX],
    "_sumY", [_sumY],
    "_count", [_count],
    "_resultOne", [_resultOne],
    "numerator", [numerator],
    "sqx1", [sqX1],
    "sqX2", [sqX2],
    "X3", [X3],
    "sqY1", [sqY1],
    "sqY2", [sqY2],
    "Y3", [Y3],
    "X3TinesY3", [X3TinesY3],
    "denominator", [denomintor],
    "CORELATION", [COREL]
)

[lennox25]

Superb! Thank you 🙂