DAX Round up and then Sum a calculated column grouped by a column

Question

Using powerbi/DAX, I need to create a measure to sum the numbers in a calculated column ('Units'), grouped by 'Order' and then round up to the next integer.

I tried (without rounding yet): OrderSummary = SUMMARIZE(tblOrder, tblOrder[OrderNumber], "TUnits", SUM(tblOrder[Units]))

I get error 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.'

Order   | Units   
---------------
 OrderA | 1.1     
 OrderA | 2.3     
 OrderB | 2.4     
 OrderB | 4.5

The total for OrderA should be 2.3 + 1.1, so 3.4 rounded up to 4
The total for OrderB should be 2.4 + 4.5, so 6.9 rounded up to 7

The output for the DAX measure should be 11

Can you share the code you've tried and show a sample of output to highlight where you're having issues? I'm sure you've tried ROUNDUP, so where are you hitting issues? — greggyb
I added what I tried. I don't think SUMMARIZE works as a measure. It does not work as shown as a calculated colum either. — oilers128

oilers128 oilers128 · Accepted Answer · 2019-09-19T19:06:50

Figured it out. I needed to use a table instead of a column for the SUMMARIZE. Steps:

Create new table

OrderSummary = SUMMARIZE(tblOrder, tblOrder[OrderNumber], "TUnits", SUM(tblOrder[Units]))

Create new colum (for rounding)

Round = ROUNDUP(OrderSummary[TUnits], 0 )

DAX Round up and then Sum a calculated column grouped by a column

1 Answers