Sum values by ID

[Anonymous]

Hello!

I've following situation:

What I want to achieve is to aggregate values by QuantityID. 2 in 1st row and 3rd row is the same, there are no spaces or anything (I've putted it via Enter Data option)

so I would see something like:

Quantity    Unit Price   Column2

2                £3                4

5                £7                5

2                £2                4

I want to do it in power bi desktop (I know I could do it via grouping in power query, but this is not an option). I know I could do it with measure in dax but this is not an option either.

I also know it will work if I remove Unit Price column, but I don't want to do it.

I want a calculate column with as stated in screen shot.

Any ideas?

Cheers!

Michal

[Anonymous · Accepted solution]

[Column 2] =
var __quantity = T[Quantity]
var __sameQuantityCount =
	COUNTROWS (
		FILTER (
			T,
			T[Quantity] = __quantity
		)
	)
var __sameQuantitySum =
	__sameQuantityCount * __quantity
RETURN
	__sameQuantitySum

Best

Darek

[Anonymous]

Thx Darek for quick answer, but I don't think this is what I was asking for.

What if I've let say 10000 rows with Quantity:

1

2

3

3

2

1

and so on...and I want to sum the quantity by each row. Not doing count though.

Cheers!

Michal

[Anonymous]

Mate,

Have a look at this:

Quantity    Unit Price   Column2

2                £3                4

5                £7                5

2                £2                4

My code does exactly what you were asking for. It does this: Get the current Quantity (I assume it's the QuantityID you were talking about but never showed it). Then, find all the rows where Quantity is the same as the current one and count the number of rows with it. Then multiply the Quantity by the number of rows and return the number. This is exactly what you wanted the code to do. Is it not?

You should be maximally clear about your requirements, otherwise you'll get what people think you wanted.

Best

Darek

[Anonymous]

Hi!

Actually...it does, but instead of counting I need sum, so I changed a little your code and it works perfect!

Column 3 =

var __quantity = 'Table from records'[Quantity]

var __sameQuantityCount =

    CALCULATE(SUM('Table from records'[Quantity]),

        FILTER (

            'Table from records',

            'Table from records'[Quantity] = __quantity

        )

    )

RETURN

    __sameQuantityCount

Thx for help 🙂

My apologies if I was not clear about what I need 🙂

Cheers!
Michal

[Anonymous]

Well, I'm happy it works for you but the logic in your formula is exactly the same as mine as much as I'm able to say. What's worse, your formula is slower than mine. You should almost NEVER put a whole table as a filter in CALCULATE. This slows calculation down big time and if you do this in regular measures, sometimes you'll be scratching your head not knowing why the formula returns seemingly nonsensical results.

Best

Darek