Calculate average based on multiselect filter within report

[Anonymous]

I have a dataset like:

Report	Date	Value
A	Dec 1 2019	1
A	Dec 3 2019	1
A	Jan 1 2020	1
B	Dec 1 2019	1
B	Jan 1 2020	1
B	Jan 2 2020	1

I need to calculate the average value for a report, where the months that are included are filtered based on a multiselect dropdown filter on the PowerBi report page. The dropdown would include Decebmer and January as the two options in this scenario.

The results I'm expecting if just December are selected is:

Report	Average
A	3
B	1

And if both December and January are selected is:

Report	Average
A	2.5
B	1.5

I've already created an expression to capture the months selected in the dropdown filter with:

CountMonths = COUNTROWS(FILTERS('Dataset'[Date]))

but it doesn't seem to be using "2" in the denomiator of:

AverageViewCount = Dataset[value]/Dataset[CountMonths]

The expression seems to just be summing the Value column when I display everything in a table.

Thanks!

[az38]

Hi @Anonymous 

How did you get Average=3 for selected December and Report A if you have only 2 rows in dataset?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Whoops, I'm not sure how I managed to mess all those assumptions up lol. The correct assumptions should be:

December only:

Report | Average

A         | 2 (2 rows over 1 month)

B         | 1 (1 row over 1 month)

December + January

Report | Average

A         | 1.5 (3 rows over 2 months)

B         | 1.5 (3 rows over 2 months)

Hope this clears things up!