Add calculated column with group by

Question

Hi,&nbsp;I have a table which looks like this (simplified version):&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Employee&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Workflow-Step&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Workflow&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Duration&nbsp;2020-01-01&nbsp; &nbsp; &nbsp;Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 52020-01-01&nbsp; &nbsp; Garcia&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22020-01-01&nbsp; &nbsp; &nbsp;Jones&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;62020-01-02&nbsp; &nbsp; &nbsp;Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 102020-01-03&nbsp; &nbsp; &nbsp;Dixon&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15&nbsp;2020-01-03&nbsp; &nbsp; &nbsp;Dixon&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15&nbsp;2020-01-03&nbsp; &nbsp; &nbsp;Dixon&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15&nbsp;2020-01-03&nbsp; &nbsp; &nbsp;Garcia&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;202020-01-03&nbsp; &nbsp; &nbsp;Garcia&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;202020-01-03&nbsp; &nbsp; &nbsp;Garcia&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20...&nbsp;There are thousands of rows with hundreds of different Workflow-Steps and dozens of different Workflows. Every Workflow Step belongs to one Workflow.I want to calculate the sum of the duration of all Workflow-Steps grouped by the Workflow. So in my example the sum of Workflow A would be 23, of Workflow B 45&nbsp;and Workflow C 60.&nbsp;&nbsp;Then I want to calculate the cumulative shares to see which Workflow-Steps account for 95% for the duration of all Workflow-Steps.Ultimately I want to add a calculated column to the initial table which indicates if a Workflow-Step belongs to Workflow-Steps which account for 95% of the durations of a Workflow.So lets say for example for Workflow A the Workflow Steps AB and AC, for Workflow B the Workflow Steps BA and BB and for Workflow C the Workflow-Step CA would account for 95% of the sum of duration. Then my table would look like this:&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Employee&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Workflow-Step&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Workflow&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Duration&nbsp; &nbsp;Relevant&nbsp;2020-01-01&nbsp; &nbsp; &nbsp;Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; No2020-01-01&nbsp; &nbsp; Garcia&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; No2020-01-01&nbsp; &nbsp; &nbsp;Jones&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yes2020-01-02&nbsp; &nbsp; &nbsp;Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Yes2020-01-03&nbsp; &nbsp; &nbsp;Dixon&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yes2020-01-03&nbsp; &nbsp; &nbsp;Dixon&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yes2020-01-03&nbsp; &nbsp; &nbsp;Dixon&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; No2020-01-03&nbsp; &nbsp; &nbsp;Garcia&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yes2020-01-03&nbsp; &nbsp; &nbsp;Garcia&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;No2020-01-03&nbsp; &nbsp; &nbsp;Garcia&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;No...&nbsp;&nbsp;How can&nbsp; I add this column?Thanks.

anonymous · Answer

You said that you want to "calculate the sum of the duration of all Workflow-Steps grouped by the Workflow."In that case, A is 23, B is 45, and C = 60.&nbsp;But you said, "A would be 23, of Workflow B 15 and Workflow C 20.&nbsp; "Why?&nbsp;Also, How did you calculate the "Yes/No" for the relevant column? It appears random to me. If you can show the calculation method, I think I can help you with the DAX code for the calculated column.&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi,&nbsp;You are right ,&nbsp;A is 23, B is 45, and C = 60., that was my mistake.&nbsp;Regarding the YES/NO column, I made the assumption that there are way more rows than I posted. - so yes, it seems random if you only take the durations I posted). If I only took the Workflow Steps I posted the sample would be too small. So lets&nbsp; assume that there are a million more rows and it turns out Workflow Steps AB, AC, BA, BB and CA are the relevant ones in their respective Workflows.&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,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 &gt;=23*0.95, then set Yes, otherwise ,set No ,right?&nbsp;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 &gt;= sumEachWorkflow * 0.95,
        "Yes",
        "No"
    )But It seems different from the expected output you gave...Could explain to me&nbsp; in more detail? Thanks.&nbsp;Best regards,Eyelyn Qin&nbsp;

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;&nbsp;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".&nbsp;Thanks.&nbsp;

anonymous · Answer

Hi Anonymous&nbsp;,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),&nbsp; Is it correct ？&nbsp;Could you use specific values as examples?&nbsp;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
            &amp;&amp; SELECTEDVALUE ( EmpTable[Duration] ) / sumEachWorkflow &lt;= sumEachWorkflow * 0.95,
        "Yes",
        "No"
    )&nbsp;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.&nbsp;Best Regards,Eyelyn Qin

Forum Discussion

Add calculated column with group by

6 Replies

Related content

Calculation groups & Column Hierarchy

Calculation Groups help

TMDL - calculated column

Grouping rows by Column and then updating Column

Create columns group