Forum Discussion
Grouping by a specific column
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.
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])
4 Replies
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])
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]))
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.
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
