Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum values by ID

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's avatar
    Anonymous
    7 years ago
    [Column 2] =
var __quantity = T[Quantity]
var __sameQuantityCount =
	COUNTROWS (
		FILTER (
			T,
			T[Quantity] = __quantity
		)
	)
var __sameQuantitySum =
	__sameQuantityCount * __quantity
RETURN
	__sameQuantitySum

    Best

    Darek

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    [Column 2] =
var __quantity = T[Quantity]
var __sameQuantityCount =
	COUNTROWS (
		FILTER (
			T,
			T[Quantity] = __quantity
		)
	)
var __sameQuantitySum =
	__sameQuantityCount * __quantity
RETURN
	__sameQuantitySum

    Best

    Darek

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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