Using if in KPI object SUM

[gasparbenito]

Hi all, 

In this table: 

ID_Material	Contador	Denominador	UMB	Sum(cantidad)	UMBase	Sum(Contador)	Palets
TOTAL	12.568.552	361.326	0	72.137.406	0	12.568.552	9.055
100008	1	1	KI	12.742	KI	1
100008	1	20	UN	12.742	KI	1
100008	72	1	PAL	12.742	KI	72	177
100008	1000	3040	KG	12.742	KI	1.000
100012	1	1	KI	6.885	KI	1
100012	1	40	UN	6.885	KI	1
100012	96	1	PAL	6.885	KI	96	72
100012	1000	2400	KG	6.885	KI	1.000
100020	1	1	KI	37.770	KI	1
100020	1	24	UN	37.770	KI	1
100020	96	1	PAL	37.770	KI	96	393
100020	1000	4560	KG	37.770	KI	1.000
100092	1	1	KI	688	KI	1
100092	1	20	UN	688	KI	1
100092	250	1	PAL	688	KI	250	3
100092	1000	1240	KG	688	KI	1.000
100094	1	1	KI	7.297	KI	1
100094	1	1	UN	7.297	KI	1
100094	72	1	PAL	7.297	KI	72	101
etc

The field 'Palets' is : 
if (UMB='PAL', SUM(cantidad)/SUM(Contador),'')

But if I write this expression in a KPI it does not work...

Could anyone give me some advice?

Thank you!

[dplr-rn]

Use set analysis instead of if statement
somewthing like
SUM({<UMB={'PAL'}>}cantidad)/SUM({<UMB={'PAL'}>}Contador)

[gasparbenito]

I had tried with that, but: 

SUM({<UMB={'PAL'}>}cantidad) = 28.587.830

SUM({<UMB={'PAL'}>}Contador)=12.502.972

KPI Result = 2.28, instead of the sum of each row (9055)

Thanks

[dplr-rn · Accepted solution]

You mean want the division to happen on each row?
why not
SUM({<UMB={'PAL'}>}cantidad/Contador)

to help share some sample data and example of what the output you desire

[gasparbenito]

yes!

Now it works perfectly!

Thank you very much

So Kind!

[vriesahm]

Try this:

Sum({<UMB = {'PAL'}>} (cantidad / Contador))

[gasparbenito]

Thank you Vriesahm

That was!