Dynamically Calculating Correlation Coefficients Based on Selections of Field Parameters

[red_ltxj]

Hi everyone,

I would like to create a function where I can dynamically calculate correlation coefficients between two indexes of countries (e.g., Cost of Living Index, Groceries Index, etc.) based on selections on two separate Fields Parameter Slicers, with the fields of those parameters as the different indexes. There are five indexes in total. I basically want this to be visualized in conjunction with a scatterplot with the X and Y axes dynamically updating based on selections on that same Fields Parameter Slicer.

I understand that there will probably be a lot of DAX involved, but is anyone able to help point me in the right direction on what to do? I've attached a screenshot of the page I'd like to achieve this on (I intend to have the dynamically calculated coefficients on the left side, with the field parameters along the X and Y axes of the scatterplot determining which indexes to calculate coefficients for. Thanks!

This is the dataset in case that's helpful: https://www.kaggle.com/datasets/myrios/cost-of-living-index-by-country-by-number-2024

[mo_measures]

I feel like there is probably a better way but maybe this will help spur some ides.... Create two new tables

X_Table ={"Cost of Living","Groceries","Purchasing Power" ...}

Y_Table = {"Cost of Living","Groceries","Purchasing Power" ...}

then use switch

X_Switch = SWITCH(SELECTEDVALUE(X_Table),

"Cost of Living",SUM(Cost of Living)

"Grocersies",SUM(Groceries)

etc

Y_Switch = SWITCH(SELECTEDVALUE(X_Table),

"Cost of Living",SUM(Cost of Living)

"Grocersies",SUM(Groceries)

etc

Then use the X Switch and Y Switch in the corrleation formula. and add slicers to page with X table, Y table.

[red_ltxj]

Hi @Anonymous 

I need the scatter chart to change dynamically according to what is selected on the two Field Parameters along the X and Y axis of the scatter chart, which I've managed to figure out on my own. The parameters are based on columns. 

However, I would like to dyanmically calculate correlation coefficients based on what is selected on those same two Field Parameters. I've attached sample data below. Thanks!

Rank	Country	Cost of Living Index	Rent Index	Cost of Living Plus Rent Index	Groceries Index	Restaurant Price Index	Local Purchasing Power Index
1	Switzerland	101.1	46.5	74.9	109.1	97	158.7
2	Bahamas	85	36.7	61.8	81.6	83.3	54.6
3	Iceland	83	39.2	62	88.4	86.8	120.3
4	Singapore	76.7	67.2	72.1	74.6	50.4	111.1
5	Barbados	76.6	19	48.9	80.8	69.4	43.5
6	Norway	76	26.2	52.1	79	73.5	114.7
7	Denmark	72.3	26.4	50.2	64.8	81.3	127.2
8	Hong Kong (China)	70.8	59.4	65.3	84.6	46.2	109.3
9	United States	70.4	41.7	56.6	75	67.2	142.3
10	Australia	70.2	33.4	52.5	77.3	62.5	127.4
11	Austria	65.1	22.5	44.7	66.4	59.3	102.4
12	Canada	64.8	33.2	49.6	71.2	61.7	103.7
13	New Zealand	64.6	25.9	46	71.9	57.1	121
14	Ireland	64.4	42.3	53.8	59.3	65.3	101.1
15	France	63.7	21	43.2	69.5	56.3	102.4
16	Puerto Rico	63.3	19.5	42.3	64.1	53.1	103.5
17	Finland	63.2	19.7	42.4	63	63.6	118
18	Netherlands	63.1	33.5	48.9	59.9	61.2	124.9
19	Israel	62.7	27.1	45.6	59.6	68	99.4
20	Luxembourg	62.4	40.5	51.9	64.1	68.7	182.5
21	Germany	62.2	24.4	44	60.8	52.8	120.2
22	United Kingdom	62	31.1	47.1	56.9	62.7	115.2
23	Belgium	61.1	20.1	41.4	57.5	66	109.8
24	South Korea	60.1	16	38.9	81.4	31.4	109.4
25	Sweden	59.3	20.9	40.9	60.8	54.7	122.6
26	Italy	56.2	19.2	38.5	57	53.3	78.3
27	United Arab Emirates	55.8	41.3	48.9	45.3	49.2	127.9

[Anonymous]

Hi @red_ltxj 

Due to some security policies, I cannot open your link. Could you please provide sample data in the form of screenshots?

In addition, I would like to confirm your requirements with you. Do you need the scatter chart to change dynamically according to the parameter you selected? Is your parameter columns or measures?

Best Regards,
Yulia Xu