Forum Discussion
Formula is incomplete
Hi everyone,
I have the following formula:
The issue is solved. Hereby, I want to thank you for your help.
First, I created a new table by using the Group By function in Power Editor. I selected only the necessary columns to be able to count the number of visits.
The measure counts the average number of visitors on a daily basis. It also counts a visitor only once if (s)he had visit multiple locations on the same day. In a table it would like this:
Date CustomerID Location Visits 1-1-2020 12345 A 1 1-1-2020 12345 B 1 1-1-2020 12345 C 1
Total 1
The formula is as follows:
Average number of visits (on daily basis) = AVERAGEX(FILTER(SUMMARIZE('GroupbyTable';[Date];'GroupbyTable'[Visits];"DC"; DISTINCTCOUNT('GroupbyTable'[CustomerID]));'GroupbyTable'[Visits] = 1);[DC])
7 Replies
Hi Anonymous
try to replace SUM() in your summarize table to AVERAGE() or MIN() or MAX() - as one vist is always 1, it should also return you 1 in any case
Hi az38 ,
I tried all options. It solved a part of the problem. It does only return one visit for a customer per day. So, the formula looks like this:
PBI average visits = AVERAGEX(SUMMARIZE(Table;[Year];[Month];[Week];[DayofWeekNr];"DC"; AVERAGE(Table[Visits]));[DC])Unfortunately, for total, it also returns 1 instead of all unique customers who visited that day.I created a new formula:IF(HASONEVALUE('Table'[Customer_ID]);[PBIaverage visits];SUMX(SUMMARIZE('Table';[Customer_ID];"Measure";[PBI average visits]);[Measure]))This didn't work either, because when you select two days of more, it returns the number of unique visitors instead of the average number of visitors of those days.There is a sample set included to make it more clear. I hope you can find the answer for this problem. This sample set shows data how it looks in the Rapport, so not in Data! That's is why measures in the table are included.The link of the sample set can is here:Anonymous
Not sure I understand correct your desired result but try
Measure = calculate(AVERAGEX( SUMMARIZE('Table','Table'[CustomerID],"VisitDays",DISTINCTCOUNT('Table'[Date])), [VisitDays] ))
Rather than doing a sum of the number of visits, maybe you could try a distinct count of the customer number?
jthomson Thanks for your suggestion. I tried distinct count, but this doesn't provide the right result. I want to know the average number of visits, bases on a daily basis. The problem with distinct count on Customer_ID is shown in the image:
102 should be 68, 38 should be 22,5 and 140 should be 90,5 (70+20)+(66+25)/2. The measure shows the number of unique customers in those two days. For the first location, there were 34 unique customers on the second day 66-(102-70).
I have added a link with an extended data set and two smaller sample sets where the problem is explained and what the measure should return as result. I hope you can find the answer for this problem.
https://drive.google.com/file/d/1NtIuGCNIiPaJMEQ3Y65ketOxYfhMKcaa/view?usp=sharing
The issue is solved. Hereby, I want to thank you for your help.
First, I created a new table by using the Group By function in Power Editor. I selected only the necessary columns to be able to count the number of visits.
The measure counts the average number of visitors on a daily basis. It also counts a visitor only once if (s)he had visit multiple locations on the same day. In a table it would like this:
Date CustomerID Location Visits 1-1-2020 12345 A 1 1-1-2020 12345 B 1 1-1-2020 12345 C 1
Total 1
The formula is as follows:
Average number of visits (on daily basis) = AVERAGEX(FILTER(SUMMARIZE('GroupbyTable';[Date];'GroupbyTable'[Visits];"DC"; DISTINCTCOUNT('GroupbyTable'[CustomerID]));'GroupbyTable'[Visits] = 1);[DC])
