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
Solved! Go to Solution.
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?
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.
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
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
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.
Join our monthly meetings and learning sessions.
We are excited to announce the Power BI Super Users!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.