topic Count Rows by Group with Filter in Power Query

Count Rows by Group with Filter

modwonka — Sun, 19 Sep 2021 12:44:37 GMT

Hello,

I have been trying solve this formula but i can't. I would appreciate if someone can help me.

This is my DDBB:

Example:

Date / ID / Type of order

Row 1: 2021-09-16 22:53:40 / 700570502001694405 / Buy
Row 2: 2021-09-15 21:53:40 / 700570502001692141 / Sell
Row 3: 2021-09-16 09:23:40 / 700570502001694405 / Buy
etc.

I would like to get a table to see how many times the buyers make one purchase?

With this structure:

1 times

2 times

3-5 times

6+ times

I tried this formula only with the first condition, to try if the formula is ok, but no...

Nº of Purchases =
VAR BuyID = CALCULATE(
COUNT(DDBB[ID]),
FILTER(DDBB,DDBB[Type of order]="Buy")
)
VAR NPurchase = SWITCH(
TRUE(),
BuyID=1, "1",
"Test"
)
RETURN NPurchase

The result is that all the buy rows are counted but I can't create the different groups. What is my mistake?

thanks!

cheers!

Re: Count Rows by Group with Filter

jennratten — Sun, 19 Sep 2021 13:12:51 GMT

Hello - this is how you can achieve the desired outcome with Power Query.

Group the purchases by ID, creating an aggregated column that counts the number of purchases (Type of Order = Buy).
Create a mapping table that has the counts and labels as they should be applied.
Merge the two tables (result below).

RESULT

MAPPING TABLE (named Count Labels)

MAPPING TABLE (Count Labels) SCRIPT

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTJQCMnMTS1WitWJVjIE8g3BfDDXCMg1QpI2BvKNdU2RREwwRExRRWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Count = _t, Label = _t]), ChangeType = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}}) in ChangeType

RESULT SCRIPT

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3NNM3MjAyVDAysjI1VtJRMjcwMDU3MDUwMjAwNLM0MTEwBQo6lVYqxeqAlZtClRtiU25kaGIIFAxOzcmBqYcab2llhN/0WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, #"Type of Order" = _t]), ChangeType = Table.TransformColumnTypes(#"Grouped Rows",{{"Date", type datetime}, {"ID", type text}, {"Type of Order", type text}}), Grouped = Table.Group( Source, {"ID"}, { { "Count of Purchases", each List.Count ( List.Select ( _[Type of Order], each _ = "Buy" ) ) } } ), ChangeTypeCount = Table.TransformColumnTypes(Grouped,{{"Count of Purchases", Int64.Type}}), Merge = Table.NestedJoin(ChangeTypeCount, {"Count of Purchases"}, #"Count Labels", {"Count"}, "Count Labels", JoinKind.LeftOuter), Expand = Table.ExpandTableColumn(Merge, "Count Labels", {"Label"}, {"Label"}) in Expand

Re: Count Rows by Group with Filter

v-yingjl — Wed, 22 Sep 2021 01:30:44 GMT

Hi modwonka ,

With the DAX formula, you can create a calculated column to group first:

No. of Purchases = VAR BuyID = CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( 'Table', 'Table'[Tyoe of order] = "Buy" && 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) ) VAR NPurchase = SWITCH ( TRUE (), BuyID = 1, "1 times", BuyID = 2, "2 times", BuyID >= 3 && BuyID <= 5, "3-5 tiems", BuyID >= 6, "6+ times" ) RETURN NPurchase

Create a measure to count:

Count = COUNTX ( SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[No. of Purchases] ), [ID] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Rows by Group with Filter

modwonka — Sat, 25 Sep 2021 21:12:16 GMT

Hi v-yingjl

Thanks for the idea! It seems it works but I have one problem...

When I set the first part in a new PowerBi only with the data of one day, everything works well. When I add more days and I tried to filter to see how the formula works in that period... it doesn't work.

You imagine I wanna see how was the nº of purchase in one specific date, could I do it with some change in the formula?

thanks!

Re: Count Rows by Group with Filter

v-yingjl — Mon, 27 Sep 2021 00:59:38 GMT

Hi modwonka ,

Modify the first formula variable like this and it should work to calculate for each day:

VAR BuyID = CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( 'Table', 'Table'[Tyoe of order] = "Buy" && 'Table'[ID] = EARLIER ( 'Table'[ID] ) && 'Table'[Date] = EARLER ('Table'[Date]) ) )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Rows by Group with Filter

modwonka — Mon, 27 Sep 2021 09:18:46 GMT

Hi v-yingjl

thanks again!

I think we're close to the solution. With that change, if I filter the data one day only, everything works. But if I try to open the selection to two or more days, the results are not ok... It doesn't count well.

Do you know why can it be happening?

Maybe we have to filter the data between two dates in the formula?

Cheers!

Re: Count Rows by Group with Filter

v-yingjl — Tue, 28 Sep 2021 01:22:17 GMT

Hi modwonka ,

Oh I forget to change the count formula:

Count = COUNT('Table'[ID]) // COUNTX ( SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[No. of Purchases] ), [ID] )

Use SUMMARIZE() would distinct the table for the specific columns, now it should be correct with the filter.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Rows by Group with Filter

modwonka — Tue, 28 Sep 2021 08:14:36 GMT

Hi v-yingjl ,

thanks again for your help. With that change, it's not ok. Maybe I explained wrong my idea...

I wanna see how is the buying frequency (nº of purchase) of the customers (ID) in a certain time.

I copy a table with the result of your fake data. You can see there are:

only 1 buyer with one transaction.
1 with 2 transactions.
1 buyer with 3 transactions.
2 buyers with 5 transactions.
1 buyer with +6 transactions.

I wanna see that nº of buyers per transaction with a range of dates. Did I explain correctly?

Cheers!

Re: Count Rows by Group with Filter

v-yingjl — Tue, 28 Sep 2021 08:31:01 GMT

Hi modwonka ,

So 3-5 times should be counted sepratly, right?

The column should be:

No. of Purchases = VAR BuyID = CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( 'Table', 'Table'[Tyoe of order] = "Buy" && 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) ) VAR NPurchase = SWITCH ( TRUE (), BuyID = 1, "1 times", BuyID = 2, "2 times", BuyID = 3, "3 times", BuyID = 4, "4 times", BuyID = 5, "5 times", BuyID >= 6, "6+ times" ) RETURN NPurchase

Count measure:

Count = COUNTX ( SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[No. of Purchases] ), [ID] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Rows by Group with Filter

modwonka — Tue, 28 Sep 2021 09:06:32 GMT

Hi v-yingjl

With this change, if you see the ID "5.000" one day (15th), it's ok. +6 times. But if you select day 16th, ID 5.000 only got 1 purchase, so it would have to show 1 time and show +6 times...

Do I explain well? Let me know if you have any doubts about the result.

thanks!

Re: Count Rows by Group with Filter

v-yingjl — Tue, 28 Sep 2021 09:30:09 GMT

Hi modwonka ,

Using 9/15-9/16 as a date range, the result should be like this?

If so, change the column formula:

No. of Purchases = VAR BuyID = CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( 'Table', 'Table'[Tyoe of order] = "Buy" && 'Table'[ID] = EARLIER ( 'Table'[ID] ) && 'Table'[Date].[Date] = EARLIER('Table'[Date].[Date]) ) ) VAR NPurchase = SWITCH ( TRUE (), BuyID = 1, "1 times", BuyID = 2, "2 times", BuyID = 3, "3 times", BuyID = 4, "4 times", BuyID = 5, "5 times", BuyID >= 6, "6+ times" ) RETURN NPurchase

Count formula:

Count = COUNTX ( SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[No. of Purchases],'Table'[Date].[Date] ), [ID] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Rows by Group with Filter

modwonka — Tue, 28 Sep 2021 13:38:53 GMT

Hi v-yingjl

thanks for your help!

You almost get the solution except by one thing. You can see the ID 5000 appears double.

5000 - 1 time
5000 - 6+ times

You would have only one 5000 with 6+ times. This customer has done 6+ purchases in the period you have choosen.

Is that possible?

Thanks!

Re: Count Rows by Group with Filter

v-yingjl — Wed, 29 Sep 2021 01:46:30 GMT

Hi modwonka ,

All right, the table total count should be the same as the left table if I understand it.

Count = CALCULATE(COUNT('Table'[ID]),'Table'[Tyoe of order] = "Buy")

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Rows by Group with Filter

modwonka — Wed, 29 Sep 2021 08:23:59 GMT

Hi v-yingjl ,

thanks again!

No, the result would be this.

Cheers!

Re: Count Rows by Group with Filter

v-yingjl — Wed, 29 Sep 2021 08:59:32 GMT

Hi modwonka ,

Re-create two measures because the No. Of Purchases should be dynamic not static and need to use ID column as context becasue only put two measures in the visual would only show the aggreated value.

No. of Purchases = VAR mindate = CALCULATE ( MIN ( 'Table'[Date].[Date] ), ALLSELECTED ( 'Table' ) ) VAR maxdate = CALCULATE ( MAX ( 'Table'[Date].[Date] ), ALLSELECTED ( 'Table' ) ) VAR BuyID = CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Tyoe of order] = "Buy" && 'Table'[ID] IN DISTINCT ( 'Table'[ID] ) && 'Table'[Date].[Date] >= mindate && 'Table'[Date].[Date] <= maxdate ) ) VAR NPurchase = SWITCH ( TRUE (), BuyID = 1, "1 times", BuyID = 2, "2 times", BuyID = 3, "3 times", BuyID = 4, "4 times", BuyID = 5, "5 times", BuyID >= 6, "6+ times" ) RETURN NPurchase

Count = COUNTX ( SUMMARIZE ( 'Table', 'Table'[ID], "A", [No. of Purchases] ), [A] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Rows by Group with Filter

modwonka — Wed, 29 Sep 2021 17:09:33 GMT

Hi v-yingjl

thanks again! that solution is ok but it's not the perfect one. Why? To have the correct result, i would have always to add ID column to the table. I don't want to do that.

I wanna have one table with only this:

Group by nº of purchases and count the nº of IDs do the action. If there are two customers that buy two times in the period i choose, i wanna see in table 2 count in 2 times row, for example.

Could you do that?

Cheers!

Re: Count Rows by Group with Filter

v-yingjl — Thu, 30 Sep 2021 01:27:59 GMT

Hi modwonka ,

I'm afraid not. As my previous posted, you need a dynamic No. of Purchases with a date slicer so it must be a measure not a calculated column.

In this situation, it must need a column as context, if only put measures in the visual, you can only see '6+ times' because there is no context in the visual for the measure to calculate.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Rows by Group with Filter

modwonka — Thu, 30 Sep 2021 10:25:52 GMT

Hi v-yingjl

ok! Bad luck...

So, i think to avoid that, we can create a table with different columns with a fixed period.

With the first code, you developed a calculated column. That calculated column can be all the period with data. And then, could we created fixed column with a certain time?

So, that would be the final result:

One column to all the period we have in column "Date".
One column to last day of "Date"
One column to last week of "Date".
One column to last month of "Date".
One column to last 6 months of "Date".
One column to last 1 year of "Date".

With these columns, we can choose the date, but at least we can have the frequency of that period.

How do you see?

thanks!!

cheers!