I have a calculated field `a/b` which makes sense at week level, where `a` is last of period metric and `b` is sum of the period metric. I need to find `avg(a/b)` for the weeks that fall under a month and not `end_of_month(a)/sum(b) for the month`.
`a` is end on hand inventory for a week, `b` is total sales for the week, `a/b` is weeks of supply. Both `a` and `b` spread along product/location/time dimensions. For `a`, I've set its regular aggregate for time as last to find the end on hand.
1. I made my `a/b` metric with regular aggregate as calculated and then monthly average metric with regular aggregate as average, but it doesn't work.
2. `average((total([a] for [week]))/(total([b] for [week])))` which is error-free but doesn't give correct result. I used total[a] because it still has to sum along other dimensions except time.
3. made individual weeks of supply metrics and then derived a new metric as (first week wos + ...+fifth week wos)/5. This does not help either.
My report is a crosstab in which weeks/month spread along the column. How can I make this metric either in FM or in Report Studio (10.1)?
I've invested 3 weeks in vain to solve this. I would really appreciate your help in this.