List of Correlations By Category

[Anonymous]

Hi Everyone!

 
So I have a Marketing Spend Table and a Sales table. They are connected by many-to-1 relationships with a date table, and many-to-1 relationships with a region table. As our marketing strategies have changed over time and from region to region, I would like to be able to find the correlation coefficient between the spend and the sale for every region and every year (the spend and sales dates are daily, so I would use the daily spend/sale as a data point). 

I have used the R integration to create a correlation plot, but then I need to check the correlation for every date and region individually. I then tried to create a correlation measure based on the correlation quick measure, which didn't work with different attributes. Here is my latest attempt: 

I am then trying to show the data in a matrix, where the rows are the dates and the columns are the regions.
I have also attached an example that I have created in Excel! 

Would anyone be able to help me with this?

Thanks for reading! 🙂

[v-frfei-msft]

Hi @Anonymous ,

Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Anonymous]

Hi @v-frfei-msft , 

Thank you for reaching out to me and offering further aid. Fortunately, I was actually able to write a DAX measure that resolved my issue. In case anyone else runs into the same issue, I will share it here. And perhaps @v-frfei-msft can help out by recommending any improvements (or renaming the post title if you think a different title is more relevant?). I used the help of @Daniil's correlation coefficient measure and modified it to meet my needs.

The important resolution that I found was that I had to write the measure within my date table. I had a many-to-one relationship between my sales and the date table, and a many-to-one relationship between my marketing spend and date table.

[Sales Count] = Count('Sales'[Amount])
[Marketing Spend] = SUM('Marketing'[Spend])

Correlation by Daily Points =

VAR v_table = SUMMARIZE(

'Date'

,'Date'[Date]

,"Sales" , [Sales Count]

,"Spend" , [Marketing Spend]

)

VAR x = SUMX(v_table,[Sales])

VAR y = SUMX(v_table,[Spend])

VAR xy = SUMX(v_table, [Sales] * [Spend])

VAR xx = SUMX(v_table, [Sales] * [Sales])

VAR yy = SUMX(v_table, [Spend] * [Spend])

VAR n = SUMX(v_table,IF([Spend] > 0 || [Sales] > 0, 1, 0))

VAR numerator =(n*xy - x*y)

VAR denominator = (n*xx - x*x)*(n*yy - y*y)

RETURN DIVIDE(numerator,SQRT(denominator))

The final result allowed for me to see the correlation between marketing spend and sales, allowing for filtering in a matrix. Here's a screenshot of what I was able to make: