I am re-developing an application that has been running on Excel. One of the reports I need to recreate (which will be published in Power BI and needs to be a direct query) is as follows;
Column "Approved Est" is calculated from a specific table, so is "Rec Value Based on Ordered Works". Column "+/- Approved Est" is a simple calculation based on the first two. This is where i need advice. There are many ways I know of to get the result, and here is what I have considered or tried.
1) Create a function that has a temporary table holding the initial columns so I can run one update that calculates the reliant columns without having to do the select for the original columns again, and ultimately returns one table.
2) Create a view with multiple unions that calculates each column. Down side is that I have to re-run the select for the original columns to give the calculated ones.
3) Create separate functions that return the calculated value for any address
Any advice gratefully received.