Percent % by Column Total in Pivot Table

[JayBrown]

Hello QS Experts, 

I'm trying to determine the %Charges of a column total in a pivot table.  I am doing the following, but it is giving me %Charges of the entire table total, not just that column.  

Expression:

(Sum({<$(set.PDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount])
-Sum({<$(set.CDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount]))

/

(Sum(TOTAL {<$(set.PDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount])
-Sum(TOTAL {<$(set.CDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount]))

How do I write the denominator in my expression to only look at the month and not the whole table?

Many thanks in advance!

[Channa]

aggr((Sum(TOTAL {<$(set.PDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount]),InsuranceClass)

[JayBrown]

Hi Channa, 

Unfortunately that did not work 😞  I updated expression to :

(Sum({<$(set.PDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount])
-Sum({<$(set.CDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount]))

/

aggr((Sum(TOTAL {<$(set.PDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount]),[Insurance Class Desc])
-aggr((Sum(TOTAL {<$(set.CDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount]),[Insurance Class Desc])

And now I get 0% values

[Channa]

can you share some sample data in xls

can you try not to show totals .may be it is taking that total while averaging

[sunny_talwar · Accepted solution]

May be this

(Sum({<$(set.PDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount])
-Sum({<$(set.CDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount]))

/

(Sum(TOTAL <[Master Month O...]> {<$(set.PDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount])
-Sum(TOTAL <[Master Month O...]>  {<$(set.CDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount]))

Can't see the whole name of your field... but replace [Master Month O...] with the your second dimension in your table

[Channa]

HI @sunny_talwar 

(Sum(TOTAL <[Master Month O...]> {<$(set.PDateType),[TX Type]={'C'},[Master Relative Month]={">=$(=-12)<=$(=-1)"}>}[Amount])

can you help me what this part does  <[Master Month O...]> 

one month /All months ??

[sunny_talwar]

---

@Channa wrote:

can you help me what this part does  <[Master Month O...]> 

It will show the repeated total for that month on each row of that specific month