Combining multiple calculated metrics to get an average.

[abf29]

I want to combine PQ/5, PQ4 and PQ4 to get an average weekly. PQ/5 is purchase quantity divided by fiscal week in Jan which equals 5 weeks. PQ/4 is for months that have four weeks using the fiscal calendar. The outcome would replace PQ/AVG Weekly which is just giving me the weekly avg for April. I would like it to be the avg of PQ5 Jan, PQ/4 Feb and PQ/4 March.

[Jennifer_Dungan · Accepted solution]

You should be able to do this... I don't have your PQ# caculated metrics, so I just threw up a PV/V calculation by month.. but you should be able to do something similar with your fiscal periods and metrics:

Now.. this is obviously a simple example.. it is also currently locked to looking at three specific time frames... I am assuming you would potentially want to see this grow as you hit new periods.. or roll the last three periods?

So you might need to extend this to have some extra logic (possibly using IF functions).... 

[abf29]

Thanks for responding. I still cannot get it to work.

[Jennifer_Dungan]

Would you be willing to share a screenshot of the combined calculated metric? Maybe there's a simple nesting issue?

[abf29]

I believe I figured it out. Now I want to take purchase quantity minus the monthly avg to get a pace.

[Jennifer_Dungan]

That should work... I see that your average is all nested inside a container

Basically:

Purchase Quantity - [Container with Average Pace Calculation]

and your "Container with Average Pace Calculation" is: 

[Container adding all the individual calculations] / 3