Forum Discussion

guitardude333's avatar
guitardude333
Frequent Visitor
5 years ago

How to add range filter and take average across columns

In Power BI I have a table in a report (Lets call it table1):

 

Person Role 
A      Cleaning
B      Cooking
C      Cleaning

I have another table (table2) in the source data which looks like (Person in table1 is linked to Person in table2):

 

Person Availability_Week40 Availability_Week41 Availability_Week42 Availability_Week43  
A      0                   0.8                 1.1                 0.4
B      1                   0.9                 0.8                 1.3
C      1                   1                   0.7                 0.6

 

I want to add a column 'Average Availability' to table1, which takes average of a specified range of weeks. So for example we can choose Week41 as start date and Week43 as end date, accordingly the 'Availability' column in table 1 will update itself to show the average availability in the chosen period.

How can I achieve this?

Need Help

3 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    guitardude333 

    You need to first UnPivot all the Availablity columns in Tables to convert from columns to rows in Power Query.

    Now it will allow you to create a measure to average the required numbers.

    ________________________

    If my answer was helpful, please click Accept it as the solution to help other members find it useful

    Click on the Thumbs-Up icon if you like this reply 🙂


    Website YouTube  LinkedIn

    • guitardude333's avatar
      guitardude333
      Frequent Visitor
      5 years ago

      Fowmy 

       

      Thanks for your reply.

      While trying to unpivot columns I get the error 

       

      "Column 'Person' in Table 'PersonTime' contains a duplicate value 'A' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

       

      This is probably because the column 'Person' is a primary key. I deleted the relationships of this table with other tables and was able to unpivot. How can I again make these relationships?

       

      Also, I am not sure how to create a dynamic measure. How can I specify start and end date for this measure and take the average of the values in between (for each person)?

       

      Could you please tell me the steps? Sorry for my ignorance, I am new to Power BI.

       

      Thank you