Grouping by a specific column

[Anonymous]

Hi

I have a table with 17 columns. 1 of them is receipt_ID and other is sales_amount. I need to sum or group all the same receipt_id into 1 sales_amount.

For example:

receipt_ID	sales_amount
11412	7
11412	4
11522	1
11522	8

I need to SUM all 11412 receipt_ID into total sales_amount which in my example would be 11 and same with 11522 which should be 9. I don't want to use query editors Group By function since I want to keep all the other columns aswell. Was thinking something in DAX.

[AilleryO]

Hi,

Not sure to get what you really need, but if it is just tohave the total per reciptID, just make a table and choose in drop down menu Sum instead of Do not summarize.

https://docs.microsoft.com/en-US/power-bi/create-reports/service-aggregates

Hope this helps

[amitchandak · Accepted solution]

@Anonymous , a simple  measure with receipt id in visual should do

sum(Table[sales_amount])

or

calculate(sum(Table[sales_amount]) , allexcept(Table, Table[receipt_id]))

a new column

sumx(filter(Table, table[receipt_id] = earlier(Table[receipt_id])) ,[sales_amount])

[Anonymous]

I tried with a measure now:
sum(Table[sales_amount])

and this seems to be working better but another problem is, I have 1 column with item quantity, to get the total sum I need to multiply sales_price with quantity.

Measure for my Sales Value:

SalesValue = SUM('Table'[SALES_PRICE])

Measure for my multiplication with quantity:

With quantity = SUMX('Table', [SalesValue] * ('Table'[QUANTITY]))

Result with total still a mess: ( looks like it just multiplies with total of quantity )

In this receipt there are only 2 items with quantity of 1, why is the total of With quantity some random number?

EDIT: I think I can figure this out on my own, thank you for the replies.

[Anonymous]

Thank you for the reply.

I tried using:
calculate(sum(Table[sales_amount]) , allexcept(Table, Table[receipt_id],Table[date]))
and all seemed to work but when I did some checking the numbers dont add up. ( made a new table and this shows all the receipt_id's not as 1.

How the heck the total is 709, also in filter i see in store level there should be 29 lines but my table only shows 20, why are the 9 hidden? If you add them all up from ( with quantity ) the total is 49,18 but PBI shows me total 709,92. Same result in the new column: calculate(sum(Table[sales_amount]) , allexcept(Table, Table[receipt_id],Table[date]))