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 ha...
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.

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.

      • Gurukiran003's avatar
        Gurukiran003
        Frequent Visitor
        5 years ago

        tq🙂