Set analytics - calculate a rate, problem with filtering

[#1]

Hello,

Below my data set:

Name	Amount	Objective
	60	20
Player1	10	0
Player2	10	0
Player3	10	10
Player4	10	10
Player5	10	0
Player6	10	0

I would like to calculate in a text object "SUM(Amount) over SUM(Objective)" to show a global achievment rate.

But I want to calculate this only for players who have an objective, in my example: only player 3 and player 4 must be used => 20 /20 => 100%

I don't manage to get the good result with these expressions:

- sum({<Objective={'>0'}>Amount)/Sum(Objective)

- If(Objective>0,sum(Amount)/Sum(Objective))

Either it gives me: 60/20 => 300% (by summing all players), or it gives me 0%

Do you have any suggestions ?

Thanks.

[sunny_talwar]

May be this:

Sum({<Name = {"=Sum(Objective) > 0"}>}Amount)/Sum(Objective)

[maxgro]

maybe

=

sum({$ <Name=P({$ <Name={"=sum(Objective)>0"}>} Name)>} Amount)

& chr(10) &

sum(Objective)

[sunny_talwar]

maxgro‌ -

Is there a difference between these two?

{<Name = {"=Sum(Objective) > 0"}>}

vs

{$ <Name=P({$ <Name={"=sum(Objective)>0"}>} Name)>}

Under any circumstances, will the above two set expression give different outputs?

[maxgro]

Don't think

And as your is most simple, your is even better

[sunny_talwar]

If they always work the same way, then yes I would think the smaller is better... but I wonder if there is any difference

[#7]

Thanks Sunny and Massimo.

It works great with the most simple, I haven't tried with the other one.

I didn't know we can mix dimension and fact when we filter in a set analysis... really useful !

Thanks !