- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

- Power BI forums
- Galleries
- Quick Measures Gallery
- Covariance

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Covariance

03-19-2018
08:21 AM

From Wikipedia on Covariance: https://en.wikipedia.org/wiki/Covariance

In probability theory and statistics, **covariance** is a measure of the joint variability of two random variables.^{[1]} If the greater values of one variable mainly correspond with the greater values of the other variable, and the same holds for the lesser values, i.e., the variables tend to show similar behavior, the covariance is positive.^{[2]} In the opposite case, when the greater values of one variable mainly correspond to the lesser values of the other, i.e., the variables tend to show opposite behavior, the covariance is negative. The sign of the covariance therefore shows the tendency in the linear relationship between the variables. The magnitude of the covariance is not easy to interpret because it is not normalized and hence depends on the magnitudes of the variables. The normalized version of the covariance, the correlation coefficient, however, shows by its magnitude the strength of the linear relation.

The covariance formula from http://mathworld.wolfram.com/Covariance.html is the following:

Thus, the following DAX measure equation performs this formula on a table (Covariance) with two columns (A and B).

Covariance = VAR AvgA = CALCULATE(AVERAGE('Covariance'[A]),ALL('Covariance')) VAR AvgB = CALCULATE(AVERAGE('Covariance'[B]),ALL('Covariance')) VAR MyTable = SUMMARIZE('Covariance','Covariance'[A],"Covariance",([A]-AvgA)*(MAX('Covariance'[B])-AvgB)/CALCULATE(COUNTROWS('Covariance'),ALL('Covariance'))) RETURN SUMX(MyTable,[Covariance])

eyJrIjoiOTU5MWM1M2QtZTdlMy00ZTY4LThkODgtZjU1YzVkOWQ5NjM2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-08-2019
11:04 AM

Thanks for providing this information.

I have tried to calculate the Covariance based on your DAX code but the system is throwing an error unkown to me. Could you please take a look at the below DAX code? (Errors in Red color)

Covariance = VAR AvgA = CALCULATE(AVERAGE('PeriodSummary-FLEXMUIRRebalancing'[Total Return]), ALL('PeriodSummary-FLEXMUIRRebalancing')) VAR AvgB = CALCULATE(AVERAGE('PeriodSummary-FLEXMUIRRebalancing'[Benchmark Return]), ALL('PeriodSummary-FLEXMUIRRebalancing')) VAR MyTable = SUMMARIZE('PeriodSummary-FLEXMUIRRebalancing', 'PeriodSummary-FLEXMUIRRebalancing'[Total Return], "PeriodSummary-FLEXMUIRRebalancing", ('PeriodSummary-FLEXMUIRRebalancing'[Total Return] - AvgA)*(MAX('PeriodSummary-FLEXMUIRRebalancing'[Benchmark Return] - AvgB) / CALCULATE(COUNTROWS('PeriodSummary-FLEXMUIRRebalancing'), ALL('PeriodSummary-FLEXMUIRRebalancing'))) RETURN

Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-08-2019
04:36 PM

I think you are missing a closing paren, in red

Covariance = VAR AvgA = CALCULATE( AVERAGE('PeriodSummary-FLEXMUIRRebalancing'[Total Return]), ALL('PeriodSummary-FLEXMUIRRebalancing') ) VAR AvgB = CALCULATE( AVERAGE('PeriodSummary-FLEXMUIRRebalancing'[Benchmark Return]), ALL('PeriodSummary-FLEXMUIRRebalancing') ) VAR MyTable = SUMMARIZE( 'PeriodSummary-FLEXMUIRRebalancing', 'PeriodSummary-FLEXMUIRRebalancing'[Total Return], "PeriodSummary-FLEXMUIRRebalancing", ('PeriodSummary-FLEXMUIRRebalancing'[Total Return] - AvgA) * (MAX('PeriodSummary-FLEXMUIRRebalancing'[Benchmark Return])- AvgB) / CALCULATE( COUNTROWS('PeriodSummary-FLEXMUIRRebalancing'), ALL('PeriodSummary-FLEXMUIRRebalancing') ) ) RETURN

Latest book!:

DAX is easy, CALCULATE makes DAX hard...