cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User I
Super User I

otherwise you can use the summarize feature in dax

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

15 REPLIES 15
Super User I
Super User I

otherwise you can use the summarize feature in dax

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Super User I
Super User I

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





Did I answer your question? Mark my post as a solution!

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?  

 

 

 





Did I answer your question? Mark my post as a solution!

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





Did I answer your question? Mark my post as a solution!

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





Did I answer your question? Mark my post as a solution!

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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




which object are you using?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors