Forum Discussion

Gurukiran003's avatar
Gurukiran003
Frequent Visitor
5 years ago
Solved

undefined

In  below image there is cloumn Name which name of empolyee N1 for whom the work has assigned from 1-1-2020 and 3-1-2020 and overal work is 20 , here need to find the average and in below table he has take Previlaged Leave on 1-1-2020 so average of workingday will be 10(20/number of day exculding Previlaged Leave) if he has not taken any leave thn average is 6.67 ( 20/number of days i.e 3 because from project start date to end date is value is 4 so (20/3=6.67)) How to get it in power bi .

General Comment
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Gurukiran003 

    Here I will give you some advice on transforming data model and how to build measures.

    Firstly, we need to build a calculated table to build a matrix visual like left up corner.

     

    Table =
GENERATE (
    SUMMARIZE ( TASK, TASK[NAME], TASK[TASK], TASK[SUB] ),
    ADDCOLUMNS (
        CALENDARAUTO (),
        "YEAR", YEAR ( [Date] ),
        "MONTH", MONTH ( [Date] ),
        "DAY", DAY ( [Date] )
    )
)

     

    Then we need to expand leave date in Employee leave Table.

    For reference: Expand a date range

    We need Subtraction +1, this is the difference between our case and sample in above blog.

    New Employee Leave Table.

    Then we add a Leave Flag column in calculated table.

     

    Leave Flag = 
IF('Table'[Date] IN VALUES('EMPLOYEE LEAVE'[Leave Day]),"L")

     

    Then build measure as below.

     

    Measure = 
VAR _START = CALCULATE(MAX(TASK[START]),FILTER(ALL(TASK),TASK[NAME]=MAX('Table'[NAME])&&TASK[TASK]=MAX('Table'[TASK])&&TASK[SUB]=MAX('Table'[SUB])))
VAR _END = CALCULATE(MAX(TASK[END]),FILTER(ALL(TASK),TASK[NAME]=MAX('Table'[NAME])&&TASK[TASK]=MAX('Table'[TASK])&&TASK[SUB]=MAX('Table'[SUB])))
VAR _DayDiff = CALCULATE(COUNT('Table'[Date]),FILTER(ALL('Table'),'Table'[NAME] = MAX(TASK[NAME])&&'Table'[TASK]=MAX(TASK[TASK])&&'Table'[SUB] = MAX(TASK[SUB])&&'Table'[Date]>=_START&&'Table'[Date]<=_END&&'Table'[Leave Flag]=BLANK()))
VAR _EFFORT = CALCULATE(SUM(TASK[EFFORT]),FILTER(ALL(TASK),TASK[NAME]=MAX('Table'[NAME])&&TASK[TASK]=MAX('Table'[TASK])&&TASK[SUB]=MAX('Table'[SUB])))
VAR _RESULT = IF(MAX('Table'[Date])>=_START&&MAX('Table'[Date])<=_END,IF(MAX('Table'[Leave Flag])=BLANK(),DIVIDE(_EFFORT,_DayDiff),"L"))
RETURN
_RESULT

     

    Build a matrix and result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • VijayP's avatar
    VijayP
    Community Champion
    5 years ago

    Gurukiran003 

    I think you need to rephrase the problem Statement. It is not clear .
    Image cannot be rotated. Some Sample Data will give more clarity alongwith the Problem Statement

    • Gurukiran003's avatar
      Gurukiran003
      Frequent Visitor
      5 years ago

      I have two table one Task Table and 2nd one is empolyee leave taken Table

      in Task table it show the name of the empolyee who working on which task and also its show start date and end date of project along with overal work hours, 

      here we need to find average of work hours example if he worked from 1-jan to 5- jan 2020 then number of days is "5 days" then overal work hour is 15 then average is 15/5=3( only possible if that person is not on leave during these 5days) , incase he to a leave on 1-jan-2020 and 4-jan-2020 then out 5days he was absent for 2days that means he worked only 3days i.e 2-jan-2020,3-jan-2020 and 5-jan-2020, now the average work is 15/3=5, that value i have to show in date wise i.e 

      1-jan-2020 = leave has to show 2-jan-2020 = 5 , 3-jan-202 =5 , 4-jan-2020 =Leave and 5-jan-2020 = 5, hope this is ok  the value has to show in leave tracker.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Gurukiran003 

        Here I will give you some advice on transforming data model and how to build measures.

        Firstly, we need to build a calculated table to build a matrix visual like left up corner.

         

        Table =
GENERATE (
    SUMMARIZE ( TASK, TASK[NAME], TASK[TASK], TASK[SUB] ),
    ADDCOLUMNS (
        CALENDARAUTO (),
        "YEAR", YEAR ( [Date] ),
        "MONTH", MONTH ( [Date] ),
        "DAY", DAY ( [Date] )
    )
)

         

        Then we need to expand leave date in Employee leave Table.

        For reference: Expand a date range

        We need Subtraction +1, this is the difference between our case and sample in above blog.

        New Employee Leave Table.

        Then we add a Leave Flag column in calculated table.

         

        Leave Flag = 
IF('Table'[Date] IN VALUES('EMPLOYEE LEAVE'[Leave Day]),"L")

         

        Then build measure as below.

         

        Measure = 
VAR _START = CALCULATE(MAX(TASK[START]),FILTER(ALL(TASK),TASK[NAME]=MAX('Table'[NAME])&&TASK[TASK]=MAX('Table'[TASK])&&TASK[SUB]=MAX('Table'[SUB])))
VAR _END = CALCULATE(MAX(TASK[END]),FILTER(ALL(TASK),TASK[NAME]=MAX('Table'[NAME])&&TASK[TASK]=MAX('Table'[TASK])&&TASK[SUB]=MAX('Table'[SUB])))
VAR _DayDiff = CALCULATE(COUNT('Table'[Date]),FILTER(ALL('Table'),'Table'[NAME] = MAX(TASK[NAME])&&'Table'[TASK]=MAX(TASK[TASK])&&'Table'[SUB] = MAX(TASK[SUB])&&'Table'[Date]>=_START&&'Table'[Date]<=_END&&'Table'[Leave Flag]=BLANK()))
VAR _EFFORT = CALCULATE(SUM(TASK[EFFORT]),FILTER(ALL(TASK),TASK[NAME]=MAX('Table'[NAME])&&TASK[TASK]=MAX('Table'[TASK])&&TASK[SUB]=MAX('Table'[SUB])))
VAR _RESULT = IF(MAX('Table'[Date])>=_START&&MAX('Table'[Date])<=_END,IF(MAX('Table'[Leave Flag])=BLANK(),DIVIDE(_EFFORT,_DayDiff),"L"))
RETURN
_RESULT

         

        Build a matrix and result is as below.

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.