Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Hi @Steven2020
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
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!
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |