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)




3 replies

    • Redeemed from the Dark Side (Power BI)
    • Obee1
    • 6 mths ago
    • Reported - view

    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. 

    • Senior Director of Product
    • Ian_Macdonald
    • 6 mths ago
    • Reported - view

    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.


    • Steve
    • 6 mths ago
    • Reported - view

    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?

        ([measures].[WSSI Plans Closing Stock Act] / ([measures].[WSSI Plans Sales Act.]+[measures].[WSSI Plans POS Rlsd Spend Act.]))

Content aside

  • Status Answered
  • 6 mths agoLast active
  • 3Replies
  • 41Views
  • 4 Following