cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How can I get Correlation Coefficient

Hi team.

I need help to create correlation visual.

 

My dataset look like below table.

 

AccountDateCategoryCost
Airline2018-01-01Compute100
Airline2018-01-01Cloud30
AIrline2018-01-01Data10
Factory2018-01-01Compute120
Factory2018-01-01Data30
Monitor2018-01-01Cloud20
Monitor2018-01-01Data10
Airline2018-02-01Compute110
Airline2018-02-01Cloud40
Factory2018-02-01Compute140
Factory2018-02-01Cloud20
Monitor2018-02-01Cloud40
Monitor2018-02-01Data10

 

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
quentin_vigne Senior Member
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

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 Senior Member
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
quentin_vigne Senior Member
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 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors