I have budget and expended budget. Both fields are sums of measures from another field called amount. The sums are handled with queries in the business/presentation model. The amount field is also set as a measure with sum in the model.
I am trying to add a percentage column that shows the %_of_budget_expended which is something like (expenditures/budget)*100. But the data is fidgety.
- need to set expenditure as ABS absolute value if it is a negative amount.
- need to set % as 0 if budget is a <= 0
- the percent needs to work at any level whether every row is shown, or whether they are grouped and filtered
I've tried various things, but I always end up with either 0 or 100% and never the actual percent of the two column values as they are displayed.
Things I've tried:
- Using calculation wizard in the business model.
- Writing my own sql in the business model
- Creating an analysis and adding a custom column formula
- Creating a workbook and creating an expression
I don't have access to the semantic model to create any heirarchies or set any granularity. I've thought about adding the column to the original view using over (by partition), but I can't really do that because the original table has about 200 million rows, and also the budget and expenditures are not in the view, they are calculated in the business model from the amount field based on other columns that define the amount as budget or expenditure (complex queries).
At this point, I don't know what else to try. I would prefer to stick with the analysis over the workbook solution if possible.
Please help me solve this! It shouldn't be this difficult!