Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Formula is incomplete

Hi everyone,   I have the following formula:   Visits = AVERAGEX( SUMMARIZE(Table; [Year]; [Month]; [Week]; [DayofWeekNr]; "DC"; SUM(Table[Visits])); [DC])   This measure should return ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi jthomson and az38 ,

     

    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:

     

    DateCustomerIDLocationVisits
    1-1-202012345A1
    1-1-202012345 B1
    1-1-202012345 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])
az38's avatar
az38
Community Champion
6 years ago

Anonymous 

Not sure I understand correct your desired result but try

Measure = calculate(AVERAGEX(
    SUMMARIZE('Table','Table'[CustomerID],"VisitDays",DISTINCTCOUNT('Table'[Date])),
    [VisitDays]
))
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi az38 

 

Thanks for your help. The measure doesn't provide the desired result unfortunately. I will provide you with a sample set of Rapport, but also with a sample set of Data. I hope that it will be more clarified for you then.