Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
HI,
I am trying to show how many days a customer visits per week.
I have all the visit data which has a line per visit (please note some customers visit more than once a day).
I would like to create a bar chart that shows how many customers have visited:
Once per week
Twice per week
Three times per week
Four times or more
and would also like this for each week of the year, to be able to show if we are increasing the amount of customer visits per week.
Can anyone help??
Thanks
Gavin
Solved! Go to Solution.
Hey,
then I would simply create an index column.
Open the query editor and edit your visit table:
In addition I would first order the date column "Ascending"
If you find my posts helpful you may consider to mark one as a solution and may also want to kudo one of the posts
Hope this helps
And then add an index column
Hey,
here you find a pbix file with a very simpliefied model.
The model just has two tables: visits and calendar
I created two calculated columns in the visit table, this is necessary, because otherwise it's not possible to "sclice" the data in a visual
WeekNo just pulls the WeekNo from the Calendar table into the Visit table, this column is hidden from the report view.
And I also create a calculated column "frequency"
frequency = SWITCH( CALCULATE( COUNT('Visit'[Customer]), ALLEXCEPT(Visit,Visit[WeekNo],Visit[Customer]) ), 1,"1 visit", 2,"2 visits", 3,"3 visits", "4 and more")
This calculation creates a frequency, by counting how often a customer has shown during a week.
The column chart shows the frequency during the weeks and linechart show that in the 2nd week there have been 3 distinct customer (just using the distinct count aggregate function on the customer in the visual)
Hope this gets you started
Hi Tom,
That is really good thank you, I am nearly there. I have one issue which is i do not have an amount of visits coulum, I just have a line of data for each visit with customer details and date of visit, how do I create this coulmn?
Thanks
Gavin
Hey,
then I would simply create an index column.
Open the query editor and edit your visit table:
In addition I would first order the date column "Ascending"
If you find my posts helpful you may consider to mark one as a solution and may also want to kudo one of the posts
Hope this helps
And then add an index column
Hi Tom,
Checking the data, I found another issue, some people are visiting more than once per day. for example someone who has visited 2 times for two days is counted as 4 or more visits but i need them to appear as 2 as they have come in for 2 days. can I do this?
Thanks
Gavin
Awesome work, thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |