Forum Discussion

lilyblue01's avatar
lilyblue01
Frequent Visitor
6 years ago
Solved

Calculating Average Transfers Using Calculated Working Days in a Period

The report I'm trying to reproduceI created this Date TableData table from databaseAll,

 

I have run into a roadblock and am looking for some help getting past this.  I am attempting to recreate an Excel document for a client in Power BI using our SQL db.  I have mostly successfully produced the report with only an issue with averages that I'm having.  

 

The report shows a count of electronic transfers of documents by the user by day.  The client requested to provide an average number of transfers for a day.  To get this number, they've been calculating the number of working days in a month.  To do this, I created a Date table then added columns to indicate whether the date is a working day or not (just excluding Saturdays and Sundays currently but plan to add a holiday table as well in the future).  

 

The problem I'm having is taking the number of working days that I've successfully calculated and using that in a measure to calculate the average by user.  The calculation should be the sum of the transfers by user divided by the number of working days.  My data is located in a single database view/table then I created the Date table and established a relationship between the 2 tables.  

 

This seems simple but I can't seem to get it to calculate since my numbers are in a column in the other table.  I tried to create a measure to calculate the number of working days but no luck there.  What is the best way to accomplish this?  Thanks in advance for any help you can provide!

 

Deanna

 

 
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi lilyblue01 ，

     

    I made a sample table for you :

    First create a calendar table and calculate which date is working day,see below:

    Then create a measure as below to calculate the average value with working days:

     

    _average = 
var a= AVERAGEX(FILTER(ALL('Table'),'Table'[isworkingday]=1),'Table'[Value])Return
IF(MAX('Table'[isworkingday])=0,0,IF(SELECTEDVALUE('Table'[Date]) in FILTERS('Date'[Date]),MAX('Table'[Value]),a))

     

    Finally you will see:

    For the related .pbix file,pls click here.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

1 Reply

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi lilyblue01 ，

     

    I made a sample table for you :

    First create a calendar table and calculate which date is working day,see below:

    Then create a measure as below to calculate the average value with working days:

     

    _average = 
var a= AVERAGEX(FILTER(ALL('Table'),'Table'[isworkingday]=1),'Table'[Value])Return
IF(MAX('Table'[isworkingday])=0,0,IF(SELECTEDVALUE('Table'[Date]) in FILTERS('Date'[Date]),MAX('Table'[Value]),a))

     

    Finally you will see:

    For the related .pbix file,pls click here.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!