Countifs/Averageifs DAX - Only one Column in Pivot

[dcheney01]

Hi All,

I'm measuring the amount of visits each employee visits one of our internal buildings. I'm attempting to do a fairly easy distinct count based on 3 criteria (date, building and staff ID). The powerpivot pivot table output shows the persons meta data (manager, org, assigned building) and in the values it show how many times they visited a given building by month(as you can see below). The values (visits) are taking up multiple columns, but I want to show only the average number of hits (average of each month) as 1 column. The average should check to see if the building they visited also matches the building they are assigned. Would also love a rolling 3 or 6 month average also.

I have the following tables in powerpivot

• Building Visited table (including date, Staff ID, building info) - obviously they can hit a building multiple times in one day...but I only want to count one time.
• Meta data (Staff ID, Name, manager, org info, assigned building, etc)
• Cleanup tables (taking building info and cleaning up the nomenclature; cleaning up staff ID)

I've heard some forums mention a calendar table would be helpful, but I don't understand why?

Any help would be great!

Output desired (example only):

ID	Last Name	First Name	Building (Assigned)	Start Date	Organization Leader	Organization	Average	Jan	Feb	Mar	Apr	May	Jun	Jul	Jan	Feb	Mar	Apr	May	Jun	Jul	Jan	Feb	Mar	Apr	May	Jun	Jul
2150	Adams	Bob	Buiding1	1/5/2000	Leader A	TTBS	5.571429	5	6	2	8	9	4	5	4	3	1	2	2	1	1		1		3
3916	Dunker	Jane	Buiding1	4/5/2012	Leader AB	TTBS	1.8	1		2		1	2	3
3732	Winters	April	Buiding1	7/12/2015		CTWR	13.85714	14	15	14	12	14	14	14
9793	Kim	Tommy	Buiding1	6/12/2018		CTWR	1	1	1	1		1	1	1	1	1		1		1			1		1
6429	Lowman	Susan	Buiding1	6/13/2018		PPWX	0									3	5	1			1				1
7963	Robinson	Fred	Buiding1	6/14/2018		PPWX	7	12	1		4		11
4323	Smith	Lars	Buiding1	6/15/2018		CTWR	11.5						6	17	9	6	5	9	7	8
4736	Ryan	Steve	Building 2	6/16/2018		PPWX	5										4		5		6
1279	Austin	Chris	Building 2	6/17/2018		PPWX	3.333333									4		4		2
7884	Styles	Michelle	Building 2	6/18/2018		CTWR	6.25								2		4	4	15				12
2936		Dave	Building 2	6/19/2018		PPWX	14.83333								15	15	15	14	15	15
6077		John	Building 2	6/20/2018		PPWX	20			1		1	1		21	20	21	19	18	20	21
7949		Tommy	Building 3	6/21/2018		TTBS	1	10	10		12		14	15	15	15	14	16	16	14	13		1		1
9191		Roy	Building 3	6/22/2018		TTBS	5.5																2		2	5	13
9139		Leon	Building 3	6/23/2018		SRS	3															4		3	2
3035		John	Building 3	6/24/2018		SRS	13.33333																14	12		14
8154		David	Building 3	6/25/2018		SRS	7.75																	12	5	7		7
6434		Gregory	Building 3	6/26/2018		SRS	1																	1

<colgroup><col><col><col><col span="2"><col><col><col span="23"></colgroup><tbody>
</tbody>

Underlying data:

ID	Last Name	First Name	Building (Assigned)	Building Visited	Date visited	Concatenate (for unique count)
2150	Adams	Bob	Buiding1	Building1	1/5/2018	2150Building143105
2150	Adams	Bob	Buiding1	Building1	1/5/2018	2150Building143105
2150	Adams	Bob	Buiding1	Building1	1/6/2018	2150Building143106
2150	Adams	Bob	Buiding1	Building1	1/7/2018	2150Building143107
2150	Adams	Bob	Buiding1	Building1	1/8/2018	2150Building143108
2150	Adams	Bob	Buiding1	Building1	1/8/2018	2150Building143108
2150	Adams	Bob	Buiding1	Building1	1/9/2018	2150Building143109
2150	Adams	Bob	Buiding1	Building2	1/5/2018	2150Building243105
2150	Adams	Bob	Buiding1	Building2	1/10/2018	2150Building243110
2150	Adams	Bob	Buiding1	Building2	1/15/2018	2150Building243115
2151	Adams	Bob	Buiding2	Building2	1/20/2018	2151Building243120
2152	Adams	Bob	Buiding3	Building2	1/30/2018	2152Building243130
2150	Adams	Bob	Buiding1	Building1	6/5/2018	2150Building143256
2150	Adams	Bob	Buiding1	Building1	6/6/2018	2150Building143257
2150	Adams	Bob	Buiding1	Building1	6/7/2018	2150Building143258
2150	Adams	Bob	Buiding1	Building1	6/8/2018	2150Building143259
2150	Adams	Bob	Buiding1	Building1	6/8/2018	2150Building143259
2150	Adams	Bob	Buiding1	Building1	6/9/2018	2150Building143260
2150	Adams	Bob	Buiding1	Building1	6/10/2018	2150Building143261
2150	Adams	Bob	Buiding1	Building2	6/11/2018	2150Building243262
2150	Adams	Bob	Buiding1	Building2	6/12/2018	2150Building243263
2150	Adams	Bob	Buiding1	Building2	6/12/2018	2150Building243263
2151	Adams	Bob	Buiding2	Building2	6/9/2018	2151Building243260
2152	Adams	Bob	Buiding3	Building2	6/10/2018	2152Building243261
2150	Adams	Bob	Buiding1	Building1	6/11/2018	2150Building143262
2150	Adams	Bob	Buiding1	Building1	6/12/2018	2150Building143263
2150	Adams	Bob	Buiding1	Building1	6/13/2018	2150Building143264
2150	Adams	Bob	Buiding1	Building1	6/14/2018	2150Building143265

<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>