How to calculate an average at a lower Time level of detail than a Discovery
Hi All,
Not sure how to create a formulate calc to do this if anyone can help. This is against a Direct SQL model. The grain of my data is:
Week, Opening Stock, Sales.
To calculate Stock Cover for the week is easy as "Opening Stock" / "Sales"
I have a date dimension that groups Weeks into Months
Month Week Stock Cover
Oct 1 12
Oct 2 8
Oct 3 9
Oct 4 15
In my Discovery I need to show the Average Stock Cover for a Month which is the Average Weekly Stock Cover e.g.
Month Avg. Weekly Stock Cover
Oct 11
Which is the Average(12, 8, 9, 15)
Thanks
Steve
3 replies
-
It sounds like you need to add (or change) the stock cover measure in the semantic model to an aggregate type of "average".
As your queries are built, it will then average all the values for that metric. If you need it to sum as well, create 2 measures - one for summation (or aggregate) and one for average.
-
Hi Steve,
Easiest way to do this is to create a regular hierarchy in your model Month --> Weeks called, say, [Month Weeks], then use the following expression in the Measures dimension:
Average(Children([Dates].^Month Weeks].currentmember),([Measures].[Stock Cover]))
Make sure to set the execution order higher than the [Stock cover] Measure calculation so that it is calculated after the lower level average.
Regular Hierarchies are often your friend when it comes to these kinds of scoped calculations. Everyone should create them as default in their models, especially around dates and calendars, it makes life super easy,
Hope that helps.
ian
-
Thanks both, before your replies arrived I used this formula which seems to work also, wondering if there are scenarios where it won't though?
average(
{AllMembers([dim_FiscalWeek].[Week_Description])},
([measures].[WSSI Plans Closing Stock Act] / ([measures].[WSSI Plans Sales Act.]+[measures].[WSSI Plans POS Rlsd Spend Act.]))
)