Aggregates of counts over time

[jonasr]

Hi,

I am trying to do some dynamic aggregation over dynamically calculated measure and I am not sure if such a thing is even possible.

Basically, I have data with something similar to the following structure where I have some snapshot of people taken each month. I need to calculate the share of people that has the characteristic left equal to one for each month and then calculate average of these share over the whole period (the actual calculation is more complex but this should suffice to illustrate the issue).

person_id snapshot_date group category left
1	 2016-01-01	 A	 ZZ	1
2	 2016-01-01	 B	 XX	1
3	 2016-01-01	 A	 ZZ	0
4	 2016-01-01	 A	 YY	0
5	 2016-02-01	 A	 ZZ	1
6	 2016-02-01	 B	 XX	0
3	 2016-02-01	 B	 ZZ	0
4	 2016-02-01	 A	 ZZ	0
7	 2016-02-01	 A	 ZZ	1
8	 2016-04-01	 B	 ZZ	0
9	 2016-04-01	 B	 XX	1
6	 2016-04-01	 A	 XX	1
3	 2016-04-01	 B	 YY	1
4	 2016-04-01	 B	 ZZ	0
8	 2016-05-03	 B	 ZZ	1
4	 2016-05-03	 B	 YY	0
10	 2016-05-03	 A	 YY	1
11	 2016-05-03	 A	 ZZ	0

I can do teh first calculation quite easily with some measure like this

Share = SUM(table[left])/COUNT(table[id])

by putting it in a table/chart with the date and group columns,

However, this is only an intermediate result. What I really need is to take these monthly shares and calculate their average over the whole period and compare these averages for each group in a column chart. So I will get column chart with A, B on X axis and 0.625 and 0.5 on Y axis.

The one way I can think of is to first calculate an intermediate calculcated table with the monthly shares and use this table in the column chart with average of the shares.

The problem is that I need to be able to filter the results using slicer with the category table (and many others not in the simple example) so it needs to be calculated dynamically with measure.

Any idea how to approach this?

Thanks.

[OwenAuger · Accepted solution]

Hi @jonasr

This may have been covered in @Greg_Deckler's reply.

The general pattern I would use for averaging a measure over the values in a particular column is:

Measure averaged by Granularity column =
AVERAGEX ( VALUES ( <Granularity column> ), [Measure] )

Using your sample table, <Granularity column> is table[snapshot date], so this measure would do what you want:

Share averaged by snapshot date =
AVERAGEX ( VALUES ( table[snapshot_date] ), [Share] )

I tested it out and I got 0.625 when group="A" and 0.5 when group = "B".

I would avoid creating a calculated table in this case.

Regards,

Owen

[jonasr]

Perfect! This is exactly what I was looking for. So simple. Thank you.

[Greg_Deckler]

See if this helps:

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149