topic Re: Best practice for filtering on a calculated expression in App Development

Best practice for filtering on a calculated expression

danelooman — Fri, 30 Aug 2019 16:53:52 GMT

I have Dimension Style, Date, Sale$, Cost$. I am making a table with

Style,
Sum(Sales$ set analysis based on date),
Sum(Cost$ set analysis based on date.),
GM%( Calculation based on previous two columns).

I would like to filter out everything that has less than 35% GM%. What is the best way to do that in the app without heading into the Data load or even the SQL script.

EDIT: Here is the actual GM% expression which returns a decimal:

(1 -
(SUM({<[DateNum]={">=$(=$(vToday)-7)"}>}[Net Sales Cost])
/
SUM({<[DateNum]={">=$(=$(vToday)-7)"}>}[Net Sales Rtl])))

Re: Best practice for filtering on a calculated expression

Anil_Babu_Samineni — Sat, 31 Aug 2019 02:53:30 GMT

Perhaps this way?

If(Num(<GM% Formula>, '#,#0%')>=Num(35/100, '#0%'), <GM% Formula>)

Re: Best practice for filtering on a calculated expression

danelooman — Tue, 03 Sep 2019 00:56:29 GMT

So this just nulls out that particular value in that particular column if it is less than 35% however I want the entire row to be removed.

Re: Best practice for filtering on a calculated expression

gmenoutis — Tue, 03 Sep 2019 10:30:56 GMT

I guess the easiest way would be to modify sum(sales),sum(cost) and gm to return 0 if gm<35%, and use "hide zeroes" on the object properties.

Else, you would have to make GM a calculated dimension and use "include nulls", which is a bit of a headache.

Re: Best practice for filtering on a calculated expression

danelooman — Tue, 03 Sep 2019 17:59:10 GMT

That is what I ended up doing. It isn't ideal because irl I have like 10 expressions so that is killing my performance but it isn't bad.

Re: Best practice for filtering on a calculated expression

gmenoutis — Wed, 04 Sep 2019 06:07:05 GMT

If you have many expressions maybe it's better to make a calculated dimension instead. That way you don't have to do future expression alterations, too.