Count aggregated data

[FabMann]

Hello, I am preatty sure this is simple but I'm blowing my mind.

I have a bunch of data which tell me each day which user click on a certain banner. 

So my table is something like:

date	user_id	banner_name
10/01/2023	u1	bname1
10/01/2023	u1	bname2
10/01/2023	u2	bname2
10/01/2023	u3	bname1
10/01/2023	u3	bname3

I can turn this table into something like this

date	user_id	number_of_clicks
10/01/2023	u1	2
10/01/2023	u2	1
10/01/2023	u3	2

What I want is a table which tells me for each day how many users click on one banner, how many click on two, ...

date	number_of_clicks	number_of_users
10/01/2023	1	1
10/01/2023	2	2
10/01/2023	3	...

Could you please help me?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi @FabMann ，

Please create a new blank query in PowerQuery and paste the code below into the advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9Q3MjAyVtJRKjUEEkl5ibmphkqxOrjkjDDljPDIGeMxEy5nDJMzwuMWLHJGeOSwmInpFixyQDNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, user_id = _t, banner_name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"user_id", type text}, {"banner_name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"date", "user_id"}, {{"number_of_clicks", each Table.RowCount(_), Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"date", "number_of_clicks"}, {{"number_of_users", each Table.RowCount(_), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"date", Order.Ascending}, {"number_of_clicks", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[amitchandak · Accepted solution]

@FabMann , I think you need dynamic segmentation

refer

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ