Groupby in a calculated column

[unknown_anony]

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

[v-heq-msft · Accepted solution]

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

[Sergii24]

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! 🙂