Forum Discussion
Dynamically Calculating Correlation Coefficients Based on Selections of Field Parameters
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
3 Replies
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 XuHi 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 Index1 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 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.
