Creating sub-table charts based on another table chart without changing calculated values

[sanp96]

Hello everyone,

I have been working on a report where I have a main table chart with fields like 'StoreName' (with 4 different categories viz. 'Parent', 'Management' etc. mentioned in the name), 'Account_ number', 'Account_total' etc.

StoreNumber

StoreName

Account_number

Accounting_Total

% of Total

I have a calculated field called '% of Total' which is  Num(sum(Accounting_Total)/(sum(Total<[Account_Number]>fabs(Accounting_Total))), '#.00%')

I am trying to split the main table into 4 smaller or sub-tables based on the different Store categories. i.e one table for only Parent stores, another for Management and so on. Basically, I am trying to break the table without affecting the '% of total' values.

One way I tried is by duplicating the entire table and then using if([StoreName] like '*parent*', StoreName) , and similar for other columns, but the '% of total' values change with this approach.

I understand the values are calculated based on a particular chart and change for a new one and hence it has been more challenging. I just wanted to know if there any way to achieve this?

Any suggestions would be really helpful.

Thanks! 

[stevejoyce]

Try not using a calculated dimension but instead filter via your chart's measures with set analysis.  Your % of Total would be your measure.

Dimensions stay as is.

"Parent" table's % of Total:

Num(sum({<[StoreName] = {'*parent*'}>} Accounting_Total)/(sum(Total<[Account_Number]>fabs(Accounting_Total))), '#.00%')

That will only calculate for "parent" storenames.  uncheck Include zero valus in Data handling setting.

[sanp96]

Hi, thank you for your reply.

I tried this approach, but I'm getting all the values as 0.00%