Calculate Covariance of Selected Values from Different Tables

[zerosugar]

Hi all,

I've been trying to create a measure for co-variance in PBI Desktop, but I can't quite seem to figure it out. I have two tables, 'Revenue' and 'Cost'. The 'Revenue' table has the columns: [Date, Line Item, Value]. The 'Cost' table has the same columns. I have a slicer for Revenue line item, and another for cost line item. How can I create a measure that gets the covariance of the cost and revenue data series based on user selection?

This is what I have so far:

Covariance: Revenue & Cost= 
VAR revenue_val = SELECTEDVALUE('revenue'[Variable])
VAR cost_val = SELECTEDVALUE('cost'[Variable])

VAR revenue_avg = 
CALCULATE(
AVERAGE('revenue'[Value]),
ALL('revenue'[Date]),
'revenue'[Line Item] = revenue_val
)

VAR cost_avg= 
CALCULATE(
AVERAGE('cost'[Value]),
ALL('cost'[Date]),
'cost'[Line Item] = cost_val
)


RETURN
SUMX('revenue', (MINX('revenue', 'revenue'[Value]) - revenue_avg)* (MINX('cost', 'cost'[Value]) - cost))/CALCULATE(COUNT('revenue'[Date]))

This doesn't seem to work though. Has anyone tried something like this before?

[v-yueyunzh-msft · Accepted solution]

Hi, @zerosugar 

Hello, according to your description, you want to calculate the covariance of two columns in two different tables.

According to my search and test, the order of values needs to be specified for covariance, that is to say, you must find the corresponding value of each line of income and cost according to your business logic. I used the Rank function during the test, according to [Line Item ] and [Date] for grouping and sorting, and then obtain the cost value corresponding to revenue. The following are the specific steps, I hope it will be useful to you:

This is my test data:

For this , we just need to use the [Line Item] field as the slicer .

And we can  create a measure like this:

Measure 2 = var _revenue_avg = AVERAGEX( 'Revenue','Revenue'[Value])
var _cost_avg = AVERAGEX('Cost','Cost'[Value])
var _count =COUNTROWS( ALLSELECTED('Revenue'))
var _revenue_list  = ALLSELECTED('Revenue')
var _cost_list = ALLSELECTED('Cost')
var _cost_list_2=ADDCOLUMNS(_cost_list ,"rank" , RANK(DENSE,_cost_list,ORDERBY([Line Item],ASC,[Date],ASC)))
var _revenue_list_2=ADDCOLUMNS(_revenue_list ,"rank" ,  RANK(DENSE,_revenue_list,ORDERBY([Line Item],ASC,[Date],ASC)))
var _revenue_list_3 =  ADDCOLUMNS(_revenue_list_2 , "cost" , var _v =  FILTER(_cost_list_2,[rank]=EARLIER([rank])) return MAXX(_v,[Value]))
var _end_table = ADDCOLUMNS(_revenue_list_3 , "v" , ([Value]-_revenue_avg)*([cost]-_cost_avg)/_count)
return
sumx(_end_table,[v])

Then we can get this :

By another , you can also refer to this:
Covariance - Microsoft Fabric Community

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly