Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
Hi, thank you for your reply.
I tried this approach, but I'm getting all the values as 0.00%