Under categories I have category A, B and C I want to compute sum(B)/sum(A) and sum(C)/sum(A) in a bar chart where x-axis is Category.

[samer qutob]

Hi ... I have two columns in my data table (categories and values). Under categories I have category A, B and C I want to compute sum(B)/sum(A) and sum©/sum(A) in a bar chart where x-axis is Category. The idea here is that we have a benchmark category 'A' and we would like to compare all other categories to this benchmark. The easiest way would be to pivot table to create a unique column for A,B and C then simply divide B over A and C over A. But this is an expensive operation since our data table is huge ... I was wondering if we can apply filtering inside custom expression. Something like this: Sum(value) / sum(value) over (All([Axis.X]) where [Axis.X] = 'A') CAN WE FILTER DATA INSIDE CUSTOM EXPRESSION THANKS

[christopher sawtelle 2]

What is the data source What about pivoting the data as you bring it in

[samer qutob]

Data source is Oracle database but we really do not care ... This may come from any source. As for pivoting table, I see 2 issues:

1. transformed tables do not automatically refresh when source table refreshes except when opening template.

2. Pivoting is time consuming in terms of execution time and it duplicates the data.

It would be amazing if custom expression allow embwdding of filtering criteria as explained in my note.

[christopher sawtelle 2]

If you do a pivot transformation as you bring in the data then you have just one table and it will reload every time you open the project or refresh it.

As far as execution time it shouldn't be much more than just brining in the data. Regarding your example the difference in load time should be almost indistinguishable.

[Mickal M.]

try :

sum(if([category]='B',[value],0))/sum(if([category]='A',[value],0)) as [b over A]and set the category axis as (Column names) instead of [category]