Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
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
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.
@Anonymous
If I understand :
You want to see Cost of Cloud correlated with Cost of Others ?
I can try to create the formula
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |