Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
hmm...
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.
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 ;/
Calculation should be fine other then your personal fields/calculations. I'm guessing your numerator should be sum([# On Hand Inventory Quantity]).
Got it! thanks a million, @stevejoyce!
Sum([# On Hand Inventory Quantity]) / Rangeavg(before(sum([# Sales Out Quantity]), 0,4))
Great! Please mark correct solution.