Summarize how many time x visits

[annekew]

Hi,

I have a dataset with the following columns:
customer_id, date, visits

I want to summarize how many customers visited once, how many customers visited twice, how many customers visited three times, etc. And visualize this in a graph

I want that only the data is included that is selected with the date filter.

So an example with the following data:

customer_id	date	visits
1	1-1-2024	1
1	2-1-2024	2
1	3-1-2024	1
2	1-1-2024	1
2	2-1-2024	3
3	3-1-2024	1
4	1-1-2024	1
5	7-1-2024	5
6	3-1-2024	1

So I want the following outcomes:
For the entire period (1-1-2024 - 7-1-2024): 
1x visited: 2
2x visited: 0
3x visited: 0 
4x visited:2
5x visited:1
6x visted: 0

For the period 1-1-2024 - 2-1-2024:
1x visited: 1
2x visited: 0
3x visited: 1
4x visited:1
5x visited:0
6x visted: 0

[lbendlin · Accepted solution]

Power BI does not support dynamic binning. You need to bring your own buckets.  Create a disconnected table with the number of visits, then create a measure to calculate the bucket population for each time period.

Your expected result seens to be a little off.

See attached

[v-denglli-msft]

Hi @annekew ,

Is my follow-up just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

[annekew]

@lbendlin Thanks! It works! 

Is it also possible to make it interactive with other visuals? Such as that if I select the row with #1 visit, the other visuals also only use the rows of the customers with in total 1 visit

[lbendlin]

You can use the bucket dimension for a slicer.

[v-denglli-msft · Accepted solution]

Thanks for the reply from lbendlin, please allow me to provide another insight.
Hi @annekew ,

Please refers to the following steps.
Create a calculated table, derived from the visits column.

The measure is as follows.

Measure = 
VAR result = CALCULATE(COUNTROWS(DISTINCT('Table'[customer_id])), 'Table'[visits]=SELECTEDVALUE('Dimtable'[visits]))
RETURN
IF(result=BLANK(),0,result)

The final result is as follows.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

Power BI does not support dynamic binning. You need to bring your own buckets.  Create a disconnected table with the number of visits, then create a measure to calculate the bucket population for each time period.

Your expected result seens to be a little off.

See attached