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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dan_hoff
Helper I
Helper I

a mystery of the ages - how to do a group by table in Power BI

Let me simplify to the most simple

 

I have a table with columns

 

Client, InvoiceDate, InvoiceNumber, InvoiceAmout

 

There are three rows

 

ClientA, 23Apr17, 123, $245

ClientB,  10May17, 124, $55.66

Client A, 15May17, 125, $99

 

I need a Power BI display, table, matrix that shows, Client and first invoice date, invoice count, last invoice date, and total Invoice Amt Total

 

So I need

 

Client A, 23Apr17, 2, 15May17,$344

Client B, 10May17,1,10May,$99

 

////////////////////////

 

It seems to be a mystery.  I know how to do in SQL

 

Select clientname, min(invoicedate), count(invoice), max(invoicedate), sum(invoiceamount) group by clientname

 

///////////////////////

 

But Power BI seems to be a mystery of the ages to do this.  Well, at least the online help and forums have never come across such a difficult and complicated scenario

 

 

 

 

1 ACCEPTED SOLUTION
vanessafvg
Super User
Super User

otherwise you can use the summarize feature in dax

 

https://msdn.microsoft.com/en-us/library/gg492171.aspx





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

15 REPLIES 15
vanessafvg
Super User
Super User

otherwise you can use the summarize feature in dax

 

https://msdn.microsoft.com/en-us/library/gg492171.aspx





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




vanessafvg
Super User
Super User

why dont you use the group by function in power query its super simple or is there a reason for you doing it in dax?

 

Capture.PNG





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




But does not that then 'destroy' my data for all subsequent tables, charts, etc.

 

I have a simple table with heaps of good data.  But I cannot show it summarised.  A mystery!

you could make a copy of  the table if you still need some of the data

 

or alternatively handle it in your visual, if you

drag your date into the visual just drop down the arrow and choose the earliest in values pane

drag your date in again and select latest in the values pane

drag in your measure invoices and drop down the arrow and select count  etc and it will summarize on that level for you.

 

does that make sense?  

 

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




absolutely make sense, but it does no group by, I get each client with the same first date.... so the grouping does not work AT ALL

I just tried the summarize....   it creates a new table from the base data, but it does not do the first date, last date AT ALL.  It simply shows ONE ROW per record... no summarize AT ALL

 

The issue I have is this:  I have given any Power BI guru a simply table of three rows.... I dont want some diatribe, three hour analysis... I just want to know how to do the group by...  beccause it JUST DONT WORK... every BI / SQL product on the planet can do 'group by'....  but not Power BI?????????????

 

I appreciate your trying to help.

It is simple, I have given a sample....  three rows I need 'group by'....  so you can do the summarise or whatever based on that.....

Hooray.... I have it.... the MSDN you sent says this

 

SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…) 

 

So, I made this

 

SumTable = SUMMARIZE(ClientDepartureReturn, ClientDepartureReturn[Debtor Code],"mindate",FIRSTDATE(ClientDepartureReturn[Bkn Date].[Date]))

 

ClientDepartReturn on DebtorCode then group by

 

This works....

 

Thanks

 

Dan

example i have just done this, works perfectly

 

Capture.PNG





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thanks for help.... the create table (summarize) from the base tables works great... thanks so much

glad you sorted





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




without your help, I would have drunk too much wine.... now I can go home!!!!!!!!!!!

yeah i get it but what you need to show me is what you actually doing, because we missing something here.

 

please post the visual example i just gave you to do 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




which object are you using?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




please post what you seeing? and how you put your fields into the object





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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