Add variance for scenario in BI Viewer component
Hi, I have two question in one:
1. I'm trying to add a variance column in a Pivot in BI Viewer for amounts that are split by 2 scenario's, actuals and budget. What is the most practical way to do this? I thought I would add a variance in my cube view used in the cube view md adapter but this is not picked up by BI Viewer.
2. Can I sort this variance column by the amount value?
1. While using CV MD adapter you won't get any calcs from CV etc. The trick is to create the following calculated fields of decimal type in BI Viewer
Actual, Expression = Iif([Scenario] = 'Actual', [Amount], 0)
Budget, Expression = Iif([Scenario] = 'Budget', [Amount], 0)
vBudget, Expression = [Actual] - [Budget]
and then if you wish can calculate vBudget% etc..Use the above calculated fields on your pivot for values and use other dimensions for columns and rows
2. The sorting options are available on the columns and rows
Glad it worked out.
Regarding sorting, you can't use "delta" in the rows. Just click on the UD1 on the Rows and you will get sorting options for your value columns. See the screen shot below: