Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

4 REPLIES 4
AilleryO
Memorable Member
Memorable Member

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

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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.

 

Anonymous
Not applicable

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.