Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
@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.
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])
Is this what you are looking for?
Thanks
Raj
@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
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,
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,
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |