How can I get Correlation Coefficient

[Anonymous]

Hi team.

I need help to create correlation visual.

My dataset look like below table.

Account	Date	Category	Cost
Airline	2018-01-01	Compute	100
Airline	2018-01-01	Cloud	30
AIrline	2018-01-01	Data	10
Factory	2018-01-01	Compute	120
Factory	2018-01-01	Data	30
Monitor	2018-01-01	Cloud	20
Monitor	2018-01-01	Data	10
Airline	2018-02-01	Compute	110
Airline	2018-02-01	Cloud	40
Factory	2018-02-01	Compute	140
Factory	2018-02-01	Cloud	20
Monitor	2018-02-01	Cloud	40
Monitor	2018-02-01	Data	10

I want to get correlation coefficient increase/decrease of cost of Category by change of Cloud cost,

(also comparison of account)

As you see, Cloud is can null by Account.

Thanks for attention 🙂

[quentin_vigne]

Hi @Anonymous

You will have to create a pearson formula using Dax 

Also I don't understand wich value you want to use for correlation ? Cost of Cloud Category ? 

- Quentin

[Anonymous]

Hi @quentin_vigne

I wonder if Cloud usage(cost) affects other category(products).

than express it as a negative or positive correlation.

And figure it out different between Account or some other features.

Thanks.

[quentin_vigne]

@Anonymous

If I understand : 

You want to see Cost of Cloud correlated with Cost of Others ? 

I can try to create the formula

[quentin_vigne]

@Anonymous

Not sure that's what you want but I don't have enough information on what you need so here we go for a pearson correlation on PowerBI (If you understand the tutorial you can edit the formula so that it adapt to your table and needs)

1 - Create the measure for your variables : 

MeasureX = SUM(YourTable[Cost])

MeasureY = COUNT(YourTable[Cost])

2 - Create the sum that we will use for the divide : 

SUM_XY = SUMX(VALUES(YourTable[Category]); [MeasureX] * [MeasureY])

SUM_XY = SUMX(VALUES(YourTable[Category]); [MeasureX] * [MeasureY])

SUM_XX = SUMX(VALUES(YourTable[Category]); [MeasureX] * [MeasureX])

Count_Category = DISTINCTCOUNT(YourTable[Category])

3 - Create divide formulas : 

Pearson_DX = ([Count_Category]*[SUM_XX]) - ([MeasureX]*[MeasureX])

Pearson_DY = ([Count_Category]*[SUM_YY]) - ([MeasureY]*[MeasureY])

Pearson_D = SQRT([Pearson_DX]*[Pearson_DY])

Pearson_N = ([Count_Category] * [SUM_XY]) - ([MeasureX]*[MeasureY])

4 - Here is our little bad boy, the final one

Pearson = DIVIDE([Pearson_N];[Pearson_D])

- Quentin