Combining multiple calculated metrics to get an average. | Community
Skip to main content
A
abf29
New Participant
April 20, 2023
Solved

Combining multiple calculated metrics to get an average.

  • April 20, 2023
  • 4 replies
  • 1646 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Jennifer_Dungan

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).... 

4 replies

Jennifer_Dungan
Jennifer_Dungan
New Participant
April 25, 2023

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

A
abf29Author
New Participant
April 25, 2023

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

 

A
abf29Author
New Participant
April 25, 2023

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

Jennifer_Dungan
Jennifer_Dungan
New Participant
April 25, 2023

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

Jennifer_Dungan
Jennifer_DunganAccepted solution
New Participant
April 20, 2023

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).... 

Terms & ConditionsAccessibility statement

Loading footer...