Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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_am...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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])

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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

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.