Forum Discussion
Adjusted R squared in Query Editor?
Hi all,
Sadly I know nothing about R, but I'd like to get the intercept, coefficients, and adjusted R squared for some variables in a query that I have in the Query Editor.
I found and liked this R script: https://stackoverflow.com/questions/48796873/multiple-linear-regression-in-power-bi
However, does anyone know whether it's possible to also generate an adjusted R squared value for the dataset and get that as well as the other numbers that are coming out at the moment?
At the moment you get two columns, 'coefficients' and 'variables'. I'm wondering whether it's possible to also get another column 'adjusted R squared'.
Thanks!
Anonymous
Please see the attached pbix. I used the same data as in the example you linked. This has 3 ways of getting to your adj R-squared value. The first is in the query editor (see the R Summary query and table in Data View). This one won't respond to slicers, but may be all you need. It references the data table, so you have have both the data table and the summary statistics available for reporting.
The other two ways are variations on using the R visual to return things other than charts. Click on each to see the R code. I didn't add a slicer (e.g., on the Date column), but these would update with slicer changes. Not very pretty but a good way to enable more statistics on your report (I hope Power BI catches up with Excel in this regard eventually).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
7 Replies
- Greg_Deckler
Community Champion
Anonymous I did some stuff with that statistical fit stuff in DAX but never in Power Query. But that was Pearson correlation coefficient. Probably doesn't help at all but this is it. https://community.powerbi.com/t5/Quick-Measures-Gallery/De-Seasonalized-Correlation-Coefficient/m-p/337829#M90
mahoneypat - You're the resident scientist, got any R chops??
- mahoneypat
Microsoft Employee
I just know enough R to be dangerous, and usually just adapt code I find online. I'll take a look for some later. One suggestion is to do your R analysis through the R visual instead of through the query editor (so it will be responsive to slicers, etc.). Please see this post I wrote on powerpivotpro.com a while ago with instructions on doing that.
https://powerpivotpro.com/2018/11/hijacking-the-r-visual/
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- mahoneypat
Anonymous
Please see the attached pbix. I used the same data as in the example you linked. This has 3 ways of getting to your adj R-squared value. The first is in the query editor (see the R Summary query and table in Data View). This one won't respond to slicers, but may be all you need. It references the data table, so you have have both the data table and the summary statistics available for reporting.
The other two ways are variations on using the R visual to return things other than charts. Click on each to see the R code. I didn't add a slicer (e.g., on the Date column), but these would update with slicer changes. Not very pretty but a good way to enable more statistics on your report (I hope Power BI catches up with Excel in this regard eventually).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- amitchandak
Super User
Anonymous , I checked how R return adjusted R square values. What I got from the format is the coefficient is a table and R square is additionally given below. Now When we run Script in R we have a way to get that value in a Variable. Now if you are controlling the data returned from R then you should able append that additional column. As, it is long since played with R, I will only be able to suggest.
