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

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

    • 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

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

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

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

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

      • erikstijnen's avatar
        erikstijnen
        New Contributor III
        3 years ago

        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.