1 Reply Latest reply on Apr 5, 2017 4:02 PM by diego.medrano

    Difference between Percent of Totals not working

    Wes Patton

      I am building a dashboard that has two crosstab tables, one for each portfolio, with a Percent of Total measure displayed.  Those work fine.  Then, I am also trying to create a table that displays the difference between the tables (A-B=C).  To get the Percent of Total measure, I have to create a calculated field using {Fixed} and the dimensions of the table.  This works fine for the values in the table, but gives values for the total row and total column that seem to be out of nowhere.  At the simplest level, I know that the total/total box is 100% for each table, so my total/total box on the comparison table should be 0.  This is not the case.  I have recreated the issue using the superstore data and attached that sample book to this question.

       

      If there is a better way to do this, please let me know.  Keep in mind that in the “real” book, I have other metrics being selected through a parameter, so I will need to do this through a calculated field.  At the bottom of this email, I’ve pasted my “real” calculation in case that needs to be considered here.  The “real” calculation works fine for the totals except for the Percent of Total portion.

       

       

      Thanks!

       

       

       

       

      if [Metric Type] = "Sum of Value" then

      sum(IF [Prod Level 3 Parameter 1] = ([Main Product]) then [Convert Paramter Metrics] END)

      -

      sum(IF [Prod Level 3 Parameter 2] = ([Main Product]) then [Convert Paramter Metrics] END)

       

      elseif [Metric Type] = "Percent of Src O/S$" then

      (sum(case [Main Product] when [Prod Level 3 Parameter 1] then ([Convert Paramter Metrics]) end)/

      sum(case [Main Product] when [Prod Level 3 Parameter 1] then ([Src Outstandings]) end)

      -

      sum(case [Main Product] when [Prod Level 3 Parameter 2] then ([Convert Paramter Metrics]) end)/

      sum(case [Main Product] when [Prod Level 3 Parameter 2] then ([Src Outstandings]) end)

      )*100

       

      elseif [Metric Type] = "Percent of Total" then

      (sum(case [Main Product] when [Prod Level 3 Parameter 1] then ({FIXED [Main Product],[Date],[Orig FICO],[Orig LTV]:sum([Convert Paramter Metrics])}) end)/

      sum(case [Main Product] when [Prod Level 3 Parameter 1] then ({FIXED [Main Product],[Date] :sum([Convert Paramter Metrics])}) end)

      -

      sum(case [Main Product] when [Prod Level 3 Parameter 2] then ({FIXED [Main Product],[Date],[Orig FICO],[Orig LTV]:sum([Convert Paramter Metrics])}) end)/

      sum(case [Main Product] when [Prod Level 3 Parameter 2] then ({FIXED [Main Product],[Date] :sum([Convert Paramter Metrics])}) end)

      )*100

       

       

       

      END