Add variance for scenario in BI Viewer component

[erikstijnen]

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?

[Sai_Maganti · Accepted solution]

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

[Sai_Maganti · Accepted solution]

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

[erikstijnen]

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:

[Sai_Maganti · Accepted solution]

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:

 

[erikstijnen]

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

[erikstijnen]

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.

[Sai_Maganti]

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.

[erikstijnen]

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

[Sai_Maganti]

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

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

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

[erikstijnen]

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?

[Sai_Maganti]

Yes step 1,4,5 are one. For adding the parameter in your expression the syntax would be ?pScenario1

[erikstijnen]

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.

[Sai_Maganti]

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 🙂

[erikstijnen]

I've tried the data grid and to be honest in my situation data grid is perfect to be honest. Again, many thanks.