Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
unknown_anony
Helper I
Helper I

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 . 

unknown_anony_0-1710318381374.png

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

1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

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:

vheqmsft_0-1711702940994.png

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

vheqmsft_1-1711703027997.png

 

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



View solution in original post

2 REPLIES 2
v-heq-msft
Community Support
Community Support

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:

vheqmsft_0-1711702940994.png

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

vheqmsft_1-1711703027997.png

 

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
Super User
Super User

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()

Sergii24_0-1710320481107.png


So your code should be similar to this:

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

 

I hope it helps! 🙂

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.