Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Have two measures and a single dimension, how do I create that table as a "variable"?

I have two different measures that show two different "kinds" of head counts. These measures work great. I also can add an additional time dimension (say year) and the measures display both measures by years perfectly. 

 

Yet, I am trying to use both measures that are (in essence) grouped by the dimension (year) to perform some additional math on the measures. I'm trying to create a linear regression model by hand (since there doesn't seem to be an easy forecast() function in Power BI)...and the line forecast ability doesn't work with the 2 measures I need. 

 

Q. Can I use the "SELECTCOLUMNS" function to create a "table" that has both measures and the dimension that I can then "loop" through to perform some additional calculations?

 

Year       x       y
2010     109    50
2011     484    258 

2012     188    177

2013     177    178

My calculations need to be able to calculate row by row so,  x * y for each row or:

109 * 50 = 5450
484 * 258 = 124872

And likewise x * x or:
109 * 109 = 11,881

Lastly, I then need to be able sum all of the x*y variables or:

 

5450 + 124872 ....

 

Any thoughts/help would be great.

 

I've found a few sources online, but they don't seem to work when I need to have these measures calculated by a specific dimension. 

 

Thanks

Jonathan

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Anonymous,
Thanks again for the assist. Unfortunately everytime I tried it the results would just spin and spin. I'm wondering if the row-by-row was an issue because the years were not the most granular part of the data...there were many additional levels of granularity "beneath" the year value. So the years and values were rolled up from the raw. 

 

Regardless, I found a work-around. It'll take a little maintenace and it involves creating many variables by hand and then doing the math by hand, but it works to calculate the forecast for the linear regression model. Below is an example.

 

Jonathan 

 

var x1 = CALCULATE([Measure1],FILTER(Table,Table[Year]="2013"))
var x2 = CALCULATE([Measure1],FILTER(Table,Table[Year]="2014"))
...
var y1 = CALCULATE([Measure2],FILTER(Table,Table[Year]="2013"))
var y2 = CALCULATE([Measure2],FILTER(Table,Table[Year]="2014"))

 

And then...

var xy1 = x1*y1
var xy2 = x2*y2
...
var xx1 = x1*x1
var xx2 = x2*x2

And
var xy_sum = xy1+xy2+xy3+xy4+xy5

 

And then building out the rest of the regression line equation from there.

 

 

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi

 

Create 2 measures with the below DAX< which calculates in row level.

 

X*Y = SUMX(Multiplication,Multiplication[X]*Multiplication[Y])

 

X*Y = SUMX(Multiplication,Multiplication[X]*Multiplication[Y])

 

Multiply.PNG 

 

Is this what you are looking for?

 

Thanks
Raj

Anonymous
Not applicable

@Anonymous,

Thanks for the response.

 

That's close, but at the end result I need to be able to "capture" as a variable the sum of x*y and the sum of x*x, so in your example I need to be able to assign 195104 and 312810 as two different variables that I can then use them for additional calculations.

 

Any thoughts on how to get that?

Jonathan  

 

 

Anonymous
Not applicable

Hi

 

Use the X*X and X*Y measure in a card , you should be getting the final total.... You can assign this measure to any variable/ measure if you need.

 

Thanks

Raj

Anonymous
Not applicable

@Anonymous,
Thanks again for the assist. Unfortunately everytime I tried it the results would just spin and spin. I'm wondering if the row-by-row was an issue because the years were not the most granular part of the data...there were many additional levels of granularity "beneath" the year value. So the years and values were rolled up from the raw. 

 

Regardless, I found a work-around. It'll take a little maintenace and it involves creating many variables by hand and then doing the math by hand, but it works to calculate the forecast for the linear regression model. Below is an example.

 

Jonathan 

 

var x1 = CALCULATE([Measure1],FILTER(Table,Table[Year]="2013"))
var x2 = CALCULATE([Measure1],FILTER(Table,Table[Year]="2014"))
...
var y1 = CALCULATE([Measure2],FILTER(Table,Table[Year]="2013"))
var y2 = CALCULATE([Measure2],FILTER(Table,Table[Year]="2014"))

 

And then...

var xy1 = x1*y1
var xy2 = x2*y2
...
var xx1 = x1*x1
var xx2 = x2*x2

And
var xy_sum = xy1+xy2+xy3+xy4+xy5

 

And then building out the rest of the regression line equation from there.

 

 

 

Anonymous
Not applicable

@Anonymous,
Thanks again for the assist. Unfortunately everytime I tried it the results would just spin and spin. I'm wondering if the row-by-row was an issue because the years were not the most granular part of the data...there were many additional levels of granularity "beneath" the year value. So the years and values were rolled up from the raw. 

 

Regardless, I found a work-around. It'll take a little maintenace and it involves creating many variables by hand and then doing the math by hand, but it works to calculate the forecast for the linear regression model. Below is an example.

 

Jonathan 

 

var x1 = CALCULATE([Measure1],FILTER(Table,Table[Year]="2013"))
var x2 = CALCULATE([Measure1],FILTER(Table,Table[Year]="2014"))
...
var y1 = CALCULATE([Measure2],FILTER(Table,Table[Year]="2013"))
var y2 = CALCULATE([Measure2],FILTER(Table,Table[Year]="2014"))

 

And then...

var xy1 = x1*y1
var xy2 = x2*y2
...
var xx1 = x1*x1
var xx2 = x2*x2

And
var xy_sum = xy1+xy2+xy3+xy4+xy5

 

And then building out the rest of the regression line equation from there.

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.