Difference between Percent of Totals not working
Wes Patton Mar 29, 2017 8:07 AMI 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 (AB=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

Pct Total Table Difference.twb 235.9 KB