Aggregate values in different row based on group

1
votes

My goal is to aggregate values in a single column based on groups of other columns.

For example my table looks like:

https://imgur.com/YbTFDh2

and the desired output would be something like this:

https://imgur.com/5It0KgW

I can do this on the same table or create a new table.

I have attemped this:

SUMMARIZECOLUMNS (
    'Sheet1 (2)'[Item],
    'Sheet1 (2)'[Name],
    "Total", SUM([Money])
)

but the error returned is: *The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.*z

EDIT

Ultimately my end goal is to create a calculated column to round the dollar up based on a series of ranges using SWITCH function.

=SWITCH(TRUE(),[Money] <= 10, 0, [Money] <= 20, 10)

...something like that.

2
powerbidax
Power BI will automatically group aggregate values depending on the dimensions you select for a visual. You don't need to create a separate measure to achieve the desired output from your screenshot. – Rory
Unfortunately, though, I have a subsequent task to create another calculated column based on the output. Is there a way we can achieve this output using summarizecolumns function? – varnhem
Could you edit your question to include detail about your further calculated column? – Rory
I've added the detail, thanks in advance! – varnhem
Ok. You don't need all those bells & whistles! You just need a simple measure, I'll include as an answer. – Rory

2 Answers

3
votes

The easiest thing to do would be create a simple measure:

Total=SUM([Money])

Then, you can view this in Power BI by putting the Name on an axis:

Like this... Table Example

Or this... Bar Chart Example

In Excel, you could use Pivot Tables to show the same thing.

1
votes

You can achieve a sum of each group rounded down to the nearest 10 using the following:

Answer =
FLOOR ( SUM ( 'Sheet1 (2)'[Money] ), 10 )

FLOOR() takes the Sum of your "Money" column as the first argument, and your required multiple (in this case 10) as the second. CEILING() and MROUND() can be used alternatively if you want to round up, or to the nearest value.

EDIT:

Based on your recent comments, perhaps something like this would be more appropriate:

Answer = 
IF(SUM('Sheet1 (2)'[Money]) < 10, 0,
IF(SUM('Sheet1 (2)'[Money]) < 20, 10,
IF(SUM('Sheet1 (2)'[Money]) < 25, 20, BLANK())))

SWITCH can only be used with constants, so you'll need to chain together some IF statements to get what you're looking for.