Percentage of sum with 2 fields

[Fabien]

Hi all,

I am trying to show what i thought was a simple % based on a specific field and week number.

The following expression returns a 'OK', however it is showing 0% which should not be the case.

What am i missing here?

=If(GetSelectedCount([Week Num])=0,

Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)
/
sum(total TEU)

,

Sum({$<[Quote Type]={"DQ"}>} TEU)
/
sum(total TEU))

[Anil_Babu_Samineni]

What if you add something

=Sum(Aggr(If(GetSelectedCount([Week Num])=0,

Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)
/
sum(total TEU)

,

Sum({$<[Quote Type]={"DQ"}>} TEU)
/
sum(total TEU)), Your Dim))

[Fabien]

Thanks for your response Anil.

Unfortunately it is still showing 0% 🤔

[Kashyap_R]

Hi Fabien

Try using this

=If(GetSelectedCount([Week Num])=0,

Sum({$<[Quote Type]={'DQ'}, [Week Num]={"$(=week(today())-1)"}>} TEU)
/
sum({1}total TEU)

,

Sum({$<[Quote Type]={'DQ'}>} TEU)
/
sum({1}total TEU))

Hope it helps

Thanks,

[asinha1991]

which part of the if else is failing?, try to put constant like 1 and 2 for example and see if it works.

if it is failing when you have no week selected, try to check this in a text box

Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)

maybe weeknum is not equating properly?

also try using single quote for [Quote Type]={'DQ'},

[Fabien]

Thank you all for your replies.

The part that is failing is the first calculation:

Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)
/
sum(total TEU)

When i select a Week Num the #KPI box works fine and displays the correct value.

I have tried other object, but nothing seems to do the trick.

[Fabien · Accepted solution]

Here is the solution, i had to divide by the total TEU for the previous week:

=If(GetSelectedCount([Week Num])=0,
(Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)/Sum({$<[Week Num]={"$(=week(today())-1)"}>} TEU)),
Sum({$<[Quote Type]={"DQ"}>} TEU)/sum(TEU))