Forum Discussion
Solved
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 th...
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:
Hi Sai,
thank you this seems like a very suitable solution as we don't have that much different scenario's in use. So I'm trying to do this, step 2 and 3 I think are ok. Step 1 and 4 is actually one step am I right? When I try to create the measure I see this unfortunately, not sure why?
Yes step 1,4,5 are one. For adding the parameter in your expression the syntax would be ?pScenario1
Perfect, that's it. Still getting used to the syntax! Many thanks. I've a last question regarding this BI project if I may.
I notice that in a pivot I can only sort rows/columns when in the BI designer, but the user cannot when viewing the dashboard. This would be great though as sometimes variance between actuals and budget is positive and sometimes it is negative.... See Q2 and Q3 below. Q3 is drilled down in the pivot below but I'm not looking for the positive variances for Q3 🙂 Any solution that is reasonably practical is welcome.
Yes it would be good to have the sorting options for users for Pivots though Data Grid has the filter and sorting options for users.
I don't have any alternate solution for this but if you find a way let me know 🙂
I've tried the data grid and to be honest in my situation data grid is perfect to be honest. Again, many thanks.