Forum Discussion

harmonvi's avatar
harmonvi
Regular Visitor
5 years ago
Solved

Divide column values every week

I need to divide weekly productivity per employee, by weekly hours per employee, repeating every week for the year. I need to keep every week's data so that I can track historical data.

 

Is there a way I can set this up to do it automatically or do I need to manually do it for every week? 

 

Productivity

This image shows the employee productivity per week

 

Hours

This image shows the employee hours worked each week 

 

The lower image shows the number of hours worked in a weeks time frame. The top image shows the employees productivity per week. I currenly have been doing the manual calculation every week -

7/4/2020 Avg Daily Productivity = Hours[7/4/2020] / (Producitvity [7/4/2020] / 8).

 I divide by 8 to get the daily instead of weekly average. This is of course combined with a lookup between employee names to pull the correct value.

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, harmonvi 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Productivity:

     

    Hours:

     

    Then you need to make the week columns selected and unpivot them in Power Query.

     

    Then you may create a measure as below.

    Result = 
DIVIDE(
    DIVIDE(
        SUM(Hours[Value]),
        CALCULATE(
            SUM(Productivity[Value]),
            FILTER(
                ALL(Productivity),
                [Name] in DISTINCT(Hours[Name])&&
                [Week] in DISTINCT(Hours[Week])
            )
        ),0
    ),8
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps,then consider Accepting it as the solution to help other members find it faster.

2 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    harmonvi if you could provided data per the links below that would be helpful. I think you are going about this the wrong way. Rather than adding more and more columns like you would do in Excel, I think I would approach this with:

    1. unpivoting the data in the date columns and creating a normalized table
    2. Have a good date table (linked to below) that would have the weekly dates marked
    3. Summarize and analyze your data in DAX using the Power BI model.

    Right tool for the job - Power Query to shape data, DAX to analyze.

     

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly
    How to provide sample data in the Power BI Forum

     

    Creating a Dynamic Date Table in Power Query

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, harmonvi 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Productivity:

     

    Hours:

     

    Then you need to make the week columns selected and unpivot them in Power Query.

     

    Then you may create a measure as below.

    Result = 
DIVIDE(
    DIVIDE(
        SUM(Hours[Value]),
        CALCULATE(
            SUM(Productivity[Value]),
            FILTER(
                ALL(Productivity),
                [Name] in DISTINCT(Hours[Name])&&
                [Week] in DISTINCT(Hours[Week])
            )
        ),0
    ),8
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps,then consider Accepting it as the solution to help other members find it faster.