Average by category with date slicer

[a-taylor]

Hi. So I am am pretty new to Power BI and I am trying to take these two columns (simplified fake versions of the real data) and get some specific visualizations but I am really stuck. I want the average number of tags per person per group, and I want it dividable by a time slicer. 

Basically, I want to be able to look at the whole thing and see what the average number of tags per person overall, and I want to be able to slice it by a time range. I want to then be able to select a group and see the average per that group. 

Table 1

person	group
a	group_1
b	group_1
c	group_2
d	group_2

Table 2

person	date	tag
a	9/6/2019	foo
a	9/6/2019	foo
a	9/6/2019	foo
a	9/4/2019	bar
a	9/2/2019	bar
b	9/6/2019	foo
b	9/6/2019	bar
b	9/5/2019	bar
b	9/6/2019	bar
c	9/4/2019	foo
c	9/2/2019	foo
c	9/1/2019	foo
d	9/4/2019	foo
d	9/5/2019	foo
d	9/3/2019	foo
d	9/5/2019	bar
d	9/2/2019	bar
d	9/3/2019	bar

I tried to make a new table with SUMMARIZE that had the totals per person, with a relationship with the table 1 making it so I could look at it per group, but I couldn't figure out how to get the date slicer to affect it. I feel like I am missing something basic but I can't figure it out. Thank you so much for your help. 

[Ashish_Mathur · Accepted solution]

Hi,

Try this.

1. Build a Calendar Table and create a relationship from the Date column of Table 2 to the Date column of the Calendar Table
2. Build your Date slicer from the Calendar Table
3. Create a relationship fro the Table 2 to Table 1 based on the Person column
4. to your visual, drag Person from Table 1
5. Write this measure = AVERAGEX(SUMMARIZE(VALUES('Table 1'[Person]),'Table 1'[Person],"ABCD",COUNTROWS('Table 2')),[ABCD])

Hope this helps.

[a-taylor]

That worked! Thank you so much! 

[Ashish_Mathur]

Thank you.

[Ashish_Mathur]

Hi,

Based on the 2 Tables that you have shared, show the exact expected result.

[a-taylor]

Sorry. I can't get the expected result to happen but I can describe it, but it would be a visualization of a bar graph with the values field being the average of the total number of tags per person, the x axis would be the group and it would be dependant on a date range slicer. 

I can figure out a table like this, that shows the total number of tags per person by using SUMMARIZE(Table2, Table2[person], COUNTA(Table2[tags])):

person	totalTags
a	5
b	4
c	3
d	6

And I can figure out how to make the graph based on that (Making a measure of AVERAGE(Table_3[totalTags]), and then having that be the value in the bar graph). But I can't figure out how to make that this depend on the date. I want to be able to look at the average per group on each day, or during a particular range. Ideally, I would be able to also have a separate visualization that has the day as the x-axis and the group as the slicer, but that is less important.