CODEWITHSUNDEEP
powerbidax
varnhem
varnhem

Reputation: 63

Aggregate values in different row based on group

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

For example my table looks like:

https://i.sstatic.net/aqSRp.jpg

and the desired output would be something like this:

https://i.sstatic.net/nsTnD.jpg

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.

Upvotes: 1

Views: 1287

Answers (2)

Rory
Rory

Reputation: 413

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.

Upvotes: 1

Ellen S
Ellen S

Reputation: 106

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.

Upvotes: 3

Related Questions