Forum Discussion

mvananaken's avatar
mvananaken
Helper II
9 years ago
Solved

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...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

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

     

    If you have any question, please feel free to ask.

     

    Best regards,
    Yuliana Gu

BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

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

                                      )

                                    )

mvananaken's avatar
mvananaken
Helper II
9 years ago

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

 

 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

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

     

    If you have any question, please feel free to ask.

     

    Best regards,
    Yuliana Gu