Forum Discussion
How to write a Correlation Measure based on two different tables with variable Store Numbers
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.
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] )
6 Replies
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
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
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
