0

I'm stuck on an Excel PowerPivot related problem that I can't seem to get a grip on:

I'm trying to measure the volume of customers at each hour and depending on which day of the week

I have a table that records the (1) date a customer visited, (2) the day of the week (Mon-Sun), (3) what hour of the day they arrived, and (4) the customer type (A,B,C etc).

Below is an example of the log and what I am trying to achieve in the pivot table:

Exhibit A

So, if two customers came the first week, and one the second week, then the measure should report 1.5 [1.5 = average(1,2)]

I am just positively stumped. I hope I've been able to explain it well enough.

Thanks for reading.

sean s
  • 15
  • 4

1 Answers1

0

Just put Day of Week and Time of Day in the Pivot table columns, and the Customer Type on the rows.

Your measures are as simple as 

Customers:=
COUNTROWS( 'MyTable' )

Average:=
[Customers] / DISTINCTCOUNT( 'MyTable'[Date] )

Min:=
MINX(
    VALUES( 'MyTable'[Date] )
    ,[Customers]
)

Max is the same as Min, but with MAXX()

greggyb
  • 3,728
  • 1
  • 11
  • 32