cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## How can I get Correlation Coefficient

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 🙂

4 REPLIES 4
Senior Member

## Re: How can I get Correlation Coefficient

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
Not applicable

## Re: How can I get Correlation Coefficient

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.

Senior Member

## Re: How can I get Correlation Coefficient

@Anonymous

If I understand :

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

I can try to create the formula

Highlighted
Senior Member

## Re: How can I get Correlation Coefficient

@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

Announcements