Forum Discussion
Grouping by a specific column
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])
Super UserAnonymous , 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.