cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lyhis
Helper IV
Helper IV

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_IDsales_amount
114127
114124
115221
115228

 

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.

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@Lyhis , 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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
AilleryO
Continued Contributor
Continued Contributor

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
Super User IV
Super User IV

@Lyhis , 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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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 )
Q.PNG
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.

 

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.

wut.PNG

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors