Calculating average measure

[jacek27031]

How to calculate and create AVG W4 average measure as INV for a given week divided into sum of SO for the last 4 weeks?

Calculated measure - [AVG W4]

Measure 1 - [SO]

Measure 2 - [INV]

Dimension - [Week Flag]

Excel example.

Thanks for any help.

[stevejoyce · Accepted solution]

Try this.  Replace sum([SO]) with your actual expression for [SO]

 [INV]  /  Rangesavg(before(sum([SO]), 0,4))

If you want to ignore filter selection on Week Flag if user makes a selection in this field, you can do this:

 [INV]  /  (Rangesavg(before(sum({<[Week Flag]=>} [SO]), 0,4)) * count(distinct [Week Flag]) )

//where multiplying by count distinct week flag will just zero out the non selected values but keep the correct calculation for your rangeavg value.

[jacek27031]

hmm...

[jacek27031]

[stevejoyce]

Sorry looks like you fixed the typo in rangeavg calculation.  are you still having an issue?

Note, i don't know if [SO] is your field name or the name/label of  your measure.  Please update sum([SO]) to the correct formula.

[jacek27031]

sorry, my bad, wrong names, should be

INV = [# On Hand Inventory Quantity]

SO = [# Sales Out Quantity]

[# On Hand Inventory Quantity] / Rangeavg(before(sum([# Sales Out Quantity]), 0,4))

however seems it's not working ;/

[stevejoyce]

Calculation should be fine other then your personal fields/calculations.  I'm guessing your numerator should be sum([# On Hand Inventory Quantity]).

[jacek27031]

Got it! thanks a million, @stevejoyce!

Sum([# On Hand Inventory Quantity]) / Rangeavg(before(sum([# Sales Out Quantity]), 0,4))

[stevejoyce]

Great!  Please mark correct solution.