Hi Immanuel Gem

Making the assumption that you have a dimension of weekday name and one that contains your dates and that you want the average of each weekday in a month or a year derived from the daily count, then we can progress.

For the existing weekday name, this is pretty straight forward. The more complicated part is doing the same thing for the calculated weekday name member, Weekend, which I am assuming is Saturday and Sunday combined.

Lets take the existing weekday average for the month first.

Create a new Formula for your model using Formulate, add the Aggregate Average block to the canvas, and select the list of elements you want to average, namely select all your dates:

Then add the Measure you are using in the datapoint:

Save your formula making sure that you save it to the Measures dimension as indicated above.

Creating the query, add you weekdays to the rows, your calculated Measure to the Columns and filter on Month and Year:

The two filters make sure that the average is calculated across the days for that year and month, the weekday names in the rows restricting the average to the days in the month for that weekday.

Great! Very easy.

We now want to create the daily average across the year for each weekday. If we want them to appear on the same query, we have make the calculation ignore the month filter. We can do this in the datapoint argument by selecting the measure and ALL the months.

Duplicate the daily average you created. 

In the datapoint argument select the month dimension, then click on the All member. This will "lock" the calculation to use all the months in  a selected year, ignoring the month filter.

 Save and add to your previous query:

We can change the Month Filter, but my daily average by year remains unchanged

If the year is changed, then the Daily Average by Year will change

Super!

Now we come to the slightly more tricky bit. The reason it's a bit more tricky is that first we have to create a new member in the weekday column called Weekend which is Saturday and Sunday added together (or whatever days make up your weekend). That bit is straightforward, but calculating the average is more complicated. When we were creating the daily average, we used the Average Block and asked for an average based on a list of dates. As each average is calculated, it takes the dates for that weekday, month and year, adds them up, then divides by the number of days for that selection. That works great as my weekdays ae aligned to the dates. But weekends have two dates each, so if I have say 4 weekends in a month, that's 8 dates, so my average will be wrong, as I'll have the total count for the 4 weekends divided by 8 instead of 4. And of course,  a weekend could span two months, so how do we handle that?

First, lets create the Weekend Member. Open a new Formulate session and drag the SUM block onto the canvas. The list we want to aggregate is the two weekdays, Saturday and Sunday:

We can leave the datapoint blank. This has the advantage that I can then use the Weekend member in other calculations. When placed in a query it will aggregate whatever Measure is being used. Note that the calculation is saved into the Weekday name dimension.

Now for the Weekend average. As discussed before, because we're basing the average on dates, I need to divide the number of dates for the weekends in a month or year by half, as a weekend is two dates. this also, conveniently, handles the case where my weekends straddle two months, in a month that has four complete weekends and a weekend that straddles the month boundary, there will be 9 days worth of data. Dividing 9 by 2 gives 4.5 which is then exact number of weekends in that month, so my average will be correct.

So to calculate the Weekend Average we take a datapoint containing the measure concerned and divide it by the count of the dates constrained by the Weekend member (which contains Saturday and Sunday) divided by two.

Let's test it:

That's looking good. We can now duplicate this Measure and lock it for all months as we did previously to create the Yearly Average.

Adding to our query gives:

The problem we now have is how to view both the Weekday and Weekend averages in the same query where they appear in the same column, as the calculations are different. We need to change the calculation from weekday to weekend when both are selected to be in the rows.

The way to do this is to inspect the member in the rows and when it is weekend use the weekend calculation and when it is a weekday, use the weekday calculation. To do this we use the currentmember function on the Weekday name dimension and test for if it is Weekend, then apply the appropriate calculation.

Duplicate and set up the yearly average the same way, using the average by year for daily and weekend Measures:

We've built a Weekend calculation in the weekday dimension and four calculations in the Measures Dimension and now we're using those in another. Sometimes it is necessary to tell Pyramid in what order to execute the calculations, Excel has a similar concept.  In the Execution panel we can set the solve order. The higher the number the later in the series the calculation is performed. In this case, the Weekend calculation needs to be performed last. This can sometimes only be found by experiment. As all the other calculations have no solve order set, setting  this to higher number will ensure it is calculated last. See the red box below:

Finally, here's the result:

Phew...

Hope that helps.

Ian