Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Add calculated column with group by

Hi,   I have a table which looks like this (simplified version):   Date            Employee         Workflow-Step           Workflow          Duration  2020-01-01     Smith                    AA...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

According to my understand, you want to set a flag when the Duration is larger than 95% of the total duration each Workflow.

For A, the total duration is 23 ,so when the Duration >=23*0.95, then set Yes, otherwise ,set No ,right? 

You could use ALLEXCEPT() to calculate each group's total:

Measure =
VAR sumEachWorkflow =
    CALCULATE (
        SUM ( EmpTable[Duration] ),
        ALLEXCEPT ( EmpTable, EmpTable[Workflow] )
    )
RETURN
    IF (
        SELECTEDVALUE ( EmpTable[Duration] ) / sumEachWorkflow >= sumEachWorkflow * 0.95,
        "Yes",
        "No"
    )

But It seems different from the expected output you gave...

Could explain to me  in more detail? Thanks.

 

Best regards,

Eyelyn Qin

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous 

 

Thanks for your time.

Not exactly, I want to set a flag with "Yes" if a Workflow-Step belongs to the Workflow-Steps which account for 95% of the duration of all Workflow-Steps in a Workflow, so I need the cumulative share. All the other Workflow-Steps, for which the sum of the durations is very small (96% - 100% of the cumulative share) should be flagged with "No".

 

Thanks.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    Sorry,I felt a little confused about your description...

    For A ,when the workflow-step begins with the same workflow, then each step accounts for 2/(2+5+6+10), 5/(2+5+6+10)

    ,6/(2+5+6+10) 10/(2+5+6+10),  Is it correct ？

     

    Could you use specific values as examples?

     

    Measure =
VAR sumEachWorkflow =
    CALCULATE (
        SUM ( EmpTable[Duration] ),
        ALLEXCEPT ( EmpTable, EmpTable[Workflow] )
    )
VAR _belongsToWorkflow =
    IF (
        LEFT ( SELECTEDVALUE ( EmpTable[Workflow-Step] ), 1 )
            = SELECTEDVALUE ( EmpTable[Workflow] ),
        1,
        0
    )
RETURN
    IF (
        _belongsToWorkflow = 1
            && SELECTEDVALUE ( EmpTable[Duration] ) / sumEachWorkflow <= sumEachWorkflow * 0.95,
        "Yes",
        "No"
    )

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,
    Eyelyn Qin

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    On the data that you have shared, show the expected result very clearly.