cancel
Showing results 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

Helper II

Calculated table with SUMX aggregation

Hello Folks,

I got a example table with (note: occurence multiple rows per employee per date is possible)

Date               Employee        hours

2016-01-01   Employee A       8

2016-01-01   Employee A       8

2016-01-01   Employee B       8

2016-01-01   Employee B       8

2016-01-01   Employee C       8

Now i want a  new calculated table with:

Date               Employee        hours

2016-01-01   Employee A       16

2016-01-01   Employee B       16

2016-01-01   Employee C       8

(background: in this table I want to made a custom column with a corrected hours of max 8 hours per date per employee), and the corrected hours are the base to make a calculed sum of (corrected) hours.

How to achieve this?

Best regards,

Marco

1 ACCEPTED SOLUTION
Microsoft Employee

Hi @mvananaken,

Based on my understanding, the "corrected hours of max 8 hours" means the max hour value is 8 even if one employee's working hours in one day is larger than 8 hours, right?

If so, you can add a calculated column in original table to generate the corrected hours. DAX formula can be:

`Column = IF('SUMX'[Hours]>8,8,'SUMX'[Hours])`

Then, new a calculate table to get the sum of hours per employee per date.

```SUMX2 =
SUMMARIZE (
'SUMX',
'SUMX'[Date],
'SUMX'[Employee],
"Total", SUM ( 'SUMX'[Column] )
)```

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Champion

Hi Marco,

You can use GroupBy in the Query Editor to get the expected results.

GROUPBYEXPECTED RESULTS

OR YOU CAN CREATE A CALCULATED COLUMN WITH THE BELOW DAX SYNTEX.

TOTAL= CALCULATE(SUM[Table[Hours]),

FILTER(Table,

Table[Employee]=EARLIER(Tabel[Employee])

)

)

Thanks & Regards,
Bhavesh

Love the Self Service BI.
New Member

Great solution! Thank you very much!

Helper II

Hello @BhaveshPatel,

Thanks for help! I really appreciate it.

The first edit query method is not possible in my case, because I want to use a related custom column  in DAX to group by, who won't exist in edit query. But in other cases: this is the best solution i guess.

The second DAX option works, but I need specific a sum of hours per employee per date. How to achieve this? (new calculated table or an other possible way)

Best regards,

Marco van Aken

Microsoft Employee

Hi @mvananaken,

Based on my understanding, the "corrected hours of max 8 hours" means the max hour value is 8 even if one employee's working hours in one day is larger than 8 hours, right?

If so, you can add a calculated column in original table to generate the corrected hours. DAX formula can be:

`Column = IF('SUMX'[Hours]>8,8,'SUMX'[Hours])`

Then, new a calculate table to get the sum of hours per employee per date.

```SUMX2 =
SUMMARIZE (
'SUMX',
'SUMX'[Date],
'SUMX'[Employee],
"Total", SUM ( 'SUMX'[Column] )
)```

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

Thanks a lot @v-yulgu-msft!!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

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

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors