Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be this:
Sum({<Name = {"=Sum(Objective) > 0"}>}Amount)/Sum(Objective)
maybe
=
sum({$ <Name=P({$ <Name={"=sum(Objective)>0"}>} Name)>} Amount)
& chr(10) &
sum(Objective)
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?
Don't think
And as your is most simple, your is even better
If they always work the same way, then yes I would think the smaller is better... but I wonder if there is any difference
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 !