Add variance for scenario in BI Viewer component

erikstijnen
New Contributor III

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?

2 ACCEPTED SOLUTIONS

Sai_Maganti
Contributor II

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

biv.png

View solution in original post

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:

biv.png

View solution in original post

13 REPLIES 13

Sai_Maganti
Contributor II

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

biv.png

Hi Sai,

thanks for this practical solution, works great this way to create calculated columns. However, I am not sure still how to sort the pivot by the values in the delta column. Yes I could add the delta column to the rows, but that makes the pivot 'unreadable'. See the two screenshots below:

1.PNG

2.PNG

 

 

 

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:

biv.png

Again many thanks, also for for this clear picture. Works perfectly.

Hi Sai,

when creating a calculated field, can you reference to parameters in way? I've made the two scenario's used in this comparison dependent on parameters, that's why. Basically my Cube View behind this BI_Viewer component contains two columns, each dependent on a parameter with a prompt.

1.PNG

 

If you are using parameters in your CV then when users run the dashboard they will get prompted and the data will be filtered to their selection. Is this what you're asking or you want to suppress the user prompts? Not sure why you want to reference parameters when creating calculated fields.

Sorry, let me explain myself. You pointed me out in order to create an actual / budget and variance column in a pivot, that I should create calculated fields:

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..

This certainly works, however ideally you want to make these formulas dependent on what the user chooses in the prompt. In other words, if he does not choose actual and budget scenario, the above formulas do not work anymore... I hope I am more clear now. Thank you

 

Ah understood. Sorry I misread your previous post.

Right, now it gets a bit tricky here. We had a similar situation and we solved it this way:

1. Create 2 calculated fields "Measure" and the second one as "Comparative".
2. Then create 2 parameters in BI Viewer pScenario1 and pScenario2 and put all your scenarios in each of those parameters using static values

p2.png
3. You can then pass the user selection to the above BI Viewer parameters

p1.png
4. Measure field formula = iif(Scenario = pScenario1, [Amount], 0)
5. Comparative field formula = iif(Scenario = pScenario2, [Amount], 0)
6. Now you can create calculated fields for Measure v Comparative, %Measure v Comparative etc..
7. You could even display the BI Viewer parameters in a text box to show the user as a legend of the scenarios user selected.

The only thing is we can't name the scenarios upfront and hence generic names and show a legend of what Measure and Comparative mean by using the TextBox.

You can even label your user prompts like Select Measure(Actual, ActBud etc), Select comparative (Budget, LY etc)

Not a 100% solution but worked for us. Here is one of our final outputs

p3.png

erikstijnen
New Contributor III

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?

2.PNG

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.

3.PNG

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.