Expression to divide one column by the total of another

[Anonymous]

I have data formatted like the first 3 columns in a pivot table, and I want to create the fourth:

Year	Value1	Value2	Expression
Other	-	15,000	7.5%
2011	-	50,000	25.0%
2012	-	40,000	20.0%
2013	200,000	30,000	15.0%
Total	200,000	135,000	67.5%

For example, the 7.5% is 15,000 (Other Years Value2) / 200,000 (Total Value1).

How do I accomplish this?  Thanks.

[Gysbert_Wassenaar]

Try sum(Value2) / sum(total Value1)

[Anonymous]

This is just giving me 0 everywhere.  Both value1 and value2 are expressions themselves, I should have mentioned that at the start.  They aren't just fields.

[Anonymous]

This is a slightly simplified version of it.  I need the LR column to all divide by the sum of EP, instead of the EP in that particular line.

[jeffmartins]

Hi chadgambone,

You need to use the expression as follow :

sum(Value2)/ sum(total Value1)

Hope this helps you.

Regards

[Gysbert_Wassenaar · Accepted solution]

Ok, two simple sum expressions. You need the total keyword in the one you divide by;

=sum(if(DataType='Actual' and Measure = 'IncdLoss',Total,0))/sum(total if(DataType='Actual',EP,0))

Actually, you can rewrite your expressions so you don't have performance inhibiting if's:

sum({<DataType={'Actual'},Measure={'IncdLoss'}>} Total) / sum({<DataType={'Actual'}>} total EP)

See attached qvw

[jeffmartins]

Hi chadgambone,

I've updated your file.

Regads

[CELAMBARASAN]

Hi,

Check the below expressions

Eprem: =Sum({<DataType={'Actual'}>} EP)

IL : =Sum({<DataType={'Actual'}, Measure={'IncdLoss'}>} [Total])

LR: =IL/Sum(TOTAL {<DataType={'Actual'}>} EP)

Hope it helps

Celambarasan

[#9]

Try this expression for  'LR' column

=IL/(sum (total (if(DataType='Actual',EP,0))))

using the keyword 'total' will help

[Anonymous]

Thanks everyone.  It took a little bit of finessing, but these techniques got it done.