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

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