% total in straight table

[rcorcoran]

Hi I have a straight table as follows:

the % change since Feb will not show correctly.  The total mode is sum of rows but I have tried all the variations here and they won't work for me.  Any help would be appreciated

The expression for this is:

if(IsNull(Pick(Match(ValueList($(vStaffCategory)),$(vStaffCategory)),



num(((Avg({$<HRPrimaryCare={15},ReferredMM={$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM))}>}ActualValue)-Avg({$<HRPrimaryCare={15},ReferredMM={$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue))/Avg({$<HRPrimaryCare={15},ReferredMM={$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue)), vNumber2), 
num(((Avg({$<HRPrimaryCare={16},ReferredMM={$(#=Max({<[HRPrimaryCare]={16}>}ReferredMM))}>}ActualValue)-Avg({$<HRPrimaryCare={16},ReferredMM={$(#=Max({<[HRPrimaryCare]={16}>}ReferredMM)-1)}>}ActualValue))/Avg({$<HRPrimaryCare={16},ReferredMM={$(#=Max({<[HRPrimaryCare]={16}>}ReferredMM)-1)}>}ActualValue)), vNumber2), 
num(((Avg({$<HRPrimaryCare={17},ReferredMM={$(#=Max({<[HRPrimaryCare]={17}>}ReferredMM))}>}ActualValue)-Avg({$<HRPrimaryCare={17},ReferredMM={$(#=Max({<[HRPrimaryCare]={17}>}ReferredMM)-1)}>}ActualValue))/Avg({$<HRPrimaryCare={17},ReferredMM={$(#=Max({<[HRPrimaryCare]={17}>}ReferredMM)-1)}>}ActualValue)), vNumber2), 
num(((Avg({$<HRPrimaryCare={18},ReferredMM={$(#=Max({<[HRPrimaryCare]={18}>}ReferredMM))}>}ActualValue)-Avg({$<HRPrimaryCare={18},ReferredMM={$(#=Max({<[HRPrimaryCare]={18}>}ReferredMM)-1)}>}ActualValue))/Avg({$<HRPrimaryCare={18},ReferredMM={$(#=Max({<[HRPrimaryCare]={18}>}ReferredMM)-1)}>}ActualValue)), vNumber2), 
num(((Avg({$<HRPrimaryCare={19},ReferredMM={$(#=Max({<[HRPrimaryCare]={19}>}ReferredMM))}>}ActualValue)-Avg({$<HRPrimaryCare={19},ReferredMM={$(#=Max({<[HRPrimaryCare]={19}>}ReferredMM)-1)}>}ActualValue))/Avg({$<HRPrimaryCare={19},ReferredMM={$(#=Max({<[HRPrimaryCare]={19}>}ReferredMM)-1)}>}ActualValue)), vNumber2), 
num(((Avg({$<HRPrimaryCare={20},ReferredMM={$(#=Max({<[HRPrimaryCare]={20}>}ReferredMM))}>}ActualValue)-Avg({$<HRPrimaryCare={20},ReferredMM={$(#=Max({<[HRPrimaryCare]={20}>}ReferredMM)-1)}>}ActualValue))/Avg({$<HRPrimaryCare={20},ReferredMM={$(#=Max({<[HRPrimaryCare]={20}>}ReferredMM)-1)}>}ActualValue)), vNumber2))),Null(),



Pick(Match(ValueList($(vStaffCategory)),$(vStaffCategory)),


num(((Sum({$<HRPrimaryCare={15},ReferredMM={$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM))}>}ActualValue)-Sum({$<HRPrimaryCare={15},ReferredMM={$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue))/Sum({$<HRPrimaryCare={15},ReferredMM={$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue)), vNumber2), 
num(((Sum({$<HRPrimaryCare={16},ReferredMM={$(#=Max({<[HRPrimaryCare]={16}>}ReferredMM))}>}ActualValue)-Sum({$<HRPrimaryCare={16},ReferredMM={$(#=Max({<[HRPrimaryCare]={16}>}ReferredMM)-1)}>}ActualValue))/Sum({$<HRPrimaryCare={16},ReferredMM={$(#=Max({<[HRPrimaryCare]={16}>}ReferredMM)-1)}>}ActualValue)), vNumber2), 
num(((Sum({$<HRPrimaryCare={17},ReferredMM={$(#=Max({<[HRPrimaryCare]={17}>}ReferredMM))}>}ActualValue)-Sum({$<HRPrimaryCare={17},ReferredMM={$(#=Max({<[HRPrimaryCare]={17}>}ReferredMM)-1)}>}ActualValue))/Sum({$<HRPrimaryCare={17},ReferredMM={$(#=Max({<[HRPrimaryCare]={17}>}ReferredMM)-1)}>}ActualValue)), vNumber2), 
num(((Sum({$<HRPrimaryCare={18},ReferredMM={$(#=Max({<[HRPrimaryCare]={18}>}ReferredMM))}>}ActualValue)-Sum({$<HRPrimaryCare={18},ReferredMM={$(#=Max({<[HRPrimaryCare]={18}>}ReferredMM)-1)}>}ActualValue))/Sum({$<HRPrimaryCare={18},ReferredMM={$(#=Max({<[HRPrimaryCare]={18}>}ReferredMM)-1)}>}ActualValue)), vNumber2), 
num(((Sum({$<HRPrimaryCare={19},ReferredMM={$(#=Max({<[HRPrimaryCare]={19}>}ReferredMM))}>}ActualValue)-Sum({$<HRPrimaryCare={19},ReferredMM={$(#=Max({<[HRPrimaryCare]={19}>}ReferredMM)-1)}>}ActualValue))/Sum({$<HRPrimaryCare={19},ReferredMM={$(#=Max({<[HRPrimaryCare]={19}>}ReferredMM)-1)}>}ActualValue)), vNumber2), 
num(((Sum({$<HRPrimaryCare={20},ReferredMM={$(#=Max({<[HRPrimaryCare]={20}>}ReferredMM))}>}ActualValue)-Sum({$<HRPrimaryCare={20},ReferredMM={$(#=Max({<[HRPrimaryCare]={20}>}ReferredMM)-1)}>}ActualValue))/Sum({$<HRPrimaryCare={20},ReferredMM={$(#=Max({<[HRPrimaryCare]={20}>}ReferredMM)-1)}>}ActualValue)), vNumber2)))

[sunny_talwar]

Just looking at the expression won't be very helpful. Would you be able to share a sample and also point out what you expected vs. what you got?

[Anonymous]

Use TOTAL <GroupingDim1,GroupingDim2,GroupingDim3,...>

[rcorcoran]

the total of this column should be 0.28% I cannot send a sample sorry

[akpofureenughwu]

Hello, How about u sending us the log file. That might help us crack this case.

[santiago_respane]

Hi,

have you tried using aggr in order to fix your totals issue?

Kind regards,

[sunny_talwar]

Would you be able to share the two expressions that you are dividing to get the percentage calculated here?

[rcorcoran]

Hi sunny

They are a bit complicated but below is the expression that will bring up the % change since feb for Medical/Dental which is correct.  it is when these correct % per division are totalled they are just totalled rather than showing the correct figure. not sure if this will make sense to you

num(((Sum({$<HRPrimaryCare={15},ReferredMM={$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM))}>}ActualValue)-Sum({$<HRPrimaryCare={15},ReferredMM={$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue))/Sum({$<HRPrimaryCare={15},ReferredMM={$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue)), vNumber2), 

[rcorcoran]

where do I use the aggr? thanks

[sunny_talwar]

Can you try this:

(Sum(Aggr(Sum({$<HRPrimaryCare={15},ReferredMM={$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM))}>}ActualValue), DivisionFieldName))

-

Sum(Aggr(Sum({$<HRPrimaryCare={15},ReferredMM={$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue), DivisionFieldName)))

/

Sum(Aggr(Sum({$<HRPrimaryCare={15},ReferredMM={$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue), DivisionFieldName))

Replace DivisionFieldName with you actual Division Field