Forum Discussion

unknown_anony's avatar
unknown_anony
Icon for Helper II rankHelper II
2 years ago
Solved

Groupby in a calculated column

Hello All,

I am working in composite model in power bi. Used summarize function to create a import table from direct query table . 

How will i group emp id and month to get the hours in one single line item insated of 4.
Note: Group by in Power query is not possible as this is a sumarrised table from direct query
Thank you
KIndly advise

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi unknown_anony ,
    Thanks for Sergii24  reply.
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Here is my test data:

    Create a new table

    Table 2 = 
SUMMARIZE(
    'Table',
    'Table'[Emp.ID],
    'Table'[SAP Month],
    "SUM", CALCULATE(
    SUM('Table'[Record hours]),
    ALLEXCEPT(
        'Table',
        'Table'[Emp.ID],
        'Table'[SAP Month]
    )
  )
)

    Final output

     

    Best regards,

    Albert He

     

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



2 Replies

  • Sergii24's avatar
    Sergii24
    Icon for Super User rankSuper User
    2 years ago

    Hi unknown_anony , could you share with us your DAX code for creating this table? Summrize function should group the table by columns you specify and then perform sum operation over Recorded Hours.

    The result from you screehshot seems to be achieved by a following expression:

    SUMMARIZE( 
        _DirectQueryTable, 
        [Emp.ID],
        [SAP Month],
        [Recorder Hours]
    )

     

    If it's so, your result is correct as PowerBI giving you unique combination of 3 of them for each row. 
    In order to sum Recorder Hours you need to specify it in Name and Expression parameters of Summarize()


    So your code should be similar to this:

    SUMMARIZE( 
        _DirectQueryTable, 
        [Emp.ID],
        [SAP Month],
        "@RecordedHours", SUM( _DirectQueryTable[Recorded Hours] )
    )

     

    I hope it helps! 🙂

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi unknown_anony ,
    Thanks for Sergii24  reply.
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Here is my test data:

    Create a new table

    Table 2 = 
SUMMARIZE(
    'Table',
    'Table'[Emp.ID],
    'Table'[SAP Month],
    "SUM", CALCULATE(
    SUM('Table'[Record hours]),
    ALLEXCEPT(
        'Table',
        'Table'[Emp.ID],
        'Table'[SAP Month]
    )
  )
)

    Final output

     

    Best regards,

    Albert He

     

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