Day of Week Average by Month Pivot

[sharmon9000]

I am trying to calculate the Day of Week average by month similar to the image below.  The weekdays are totaled up for each month and then the day total is divided by the monthly total.  I am able to do this in excel but was trying to do it in PowerBI to help automate the process.  I am able to get the data into PowerBI and can get it to work if I filter to a single month and using % of Column total, but this falls apart when trying to use a matrix.

Is this possible in PowerBI?

Excel Pivot Table

Sample Data:

AccrualDate   AccrualDoW   AccrualMonth      Item     Total     
2019-01-01    Tuesday      January           A        19345.56 
2019-01-01    Tuesday      January           B        12345.37
2019-01-02    Wednesday    January           A        38433.45   
2019-01-02    Wednesday    January           B        37567.32
2019-01-03    Thursday     January           A        34853.34  
2019-01-03    Thursday     January           B        78893.87 
2019-01-04    Friday       January           A        43873.46
2019-01-04    Friday       January           B        34853.34 
2019-02-03    Monday       February          A        33457.32
2019-02-03    Monday       February          B        78277.56
2019-02-03    Tuesday      February          A        93847.32
2019-02-03    Tuesday      February          B        67632.98
2019-02-03    Wednesday    February          A        38447.72
2019-02-03    Wednesday    February          B        44745.02
2019-02-03    Thursday     February          A        23474.52
2019-02-03    Thursday     February          B        37475.12
2019-02-03    Friday       February          A        63475.92
2019-02-03    Friday       February          B        72975.43

* this continues for everyday of the week.  

[RobbeVL]

Hi,

To answer your question, yes this is possible.

Robbe

[sharmon9000]

@RobbeVL

Any ideas on what approach I should take to get this to work in PowerBI?

[RobbeVL]

Some sample data would help us help you a long way! 🙂

[sharmon9000]

@RobbeVL I appologize for not attaching some sample data.

The SQL query returns data in the format below:

AccrualDate   AccrualDoW   AccrualMonth      Item     Total     
2019-01-01    Tuesday      January           A        19345.56 
2019-01-01    Tuesday      January           B        12345.37
2019-01-02    Wednesday    January           A        38433.45   
2019-01-02    Wednesday    January           B        37567.32
2019-01-03    Thursday     January           A        34853.34  
2019-01-03    Thursday     January           B        78893.87 
2019-01-04    Friday       January           A        43873.46
2019-01-04    Friday       January           B        34853.34 

* this continues for everyday of the week.  

For starters I was just going to try and use the WeekDay Total for both Items, and then eventually have other columns showing Item A, and Item B's WeekDay average

[RobbeVL]

With the data you gave me I was able to produce this matrix table, without using any measure.

Does this make sense to you ? 

[sharmon9000]

@RobbeVL  I have updated the Sample data to include another month, and inserted it into the original post.

Thanks again for looking into this.

[sharmon9000]

@RobbeVL  I could also get it to work for a single month, but when it gets into the next month of February, the % of column total would not work anymore.  I didnt continue the sample data into another month, but it is continuous.