Forum Discussion

erikstijnen's avatar
erikstijnen
New Contributor III
3 years ago

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...
BI Viewer
  • Sai_Maganti's avatar
    Sai_Maganti
    3 years ago

    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's avatar
    Sai_Maganti
    3 years ago

    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:

     

Sai_Maganti's avatar
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

erikstijnen's avatar
erikstijnen
New Contributor III
3 years ago

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's avatar
    Sai_Maganti
    Contributor II
    3 years ago

    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's avatar
      erikstijnen
      New Contributor III
      3 years ago

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