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

# Difference between Percent of Totals not working

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

• ###### 1. Re: Difference between Percent of Totals not working

Hey Wes,

Granularity issues with the build in total buttons are pretty common. You're better off creating your totals from scratch. To understand why the totals are acting wonky, here's a useful thread.

-Diego