3 Replies Latest reply on Mar 6, 2017 11:48 AM by Jim Dehner

Calculating profit & sales tiers/levels using calculated fields

Hi there,

I’m trying to calculate a gross profit, based on different sales levels per month. The logic is the following:

For sales less than \$100,000 in a given month, then gross profit = sales * 1%

For sales over \$100,000 then sales % .5%

So for if in February sales were \$150,000, gross profit should be \$1250

I’ve put together the following LOD expression, but I’m not getting the correct results because I don’t think it’s calculating the sales based on the month (instead it's doing cumulative). In it, I’m assuming that sales for the month (product amount) is always greater than 100,000, but I’d love your tips on how to do the whole calculation without any assumptions.

{ FIXED [Month Paid date],[Application Code],[Status]:SUM([Product Amount]-100000)*.005+1000 }

• 1. Re: Calculating profit & sales tiers/levels using calculated fields

Hi Sarah

You don't really need an LOD calculation - the calculation below will work

if SUM([Sales])>100000 then 1000+.005*(sum([Sales]) -100000)

else .01*SUM([Sales])  end

Then you create you vis including MONTH on column or row shelf - see below a Superstore example

Let me know if this helped

Jim

• 2. Re: Calculating profit & sales tiers/levels using calculated fields

Of course! I tried that at the beginning but must have put in too many 0's or something because the calculations didn't come out correct. Thanks!

• 3. Re: Calculating profit & sales tiers/levels using calculated fields

Sure -