Hello, I've created a metric which is the Avergae ...
# gooddata-platform
Hello, I've created a metric which is the Avergae Time to Control, which I want to calculate for a certain Userprofile. It works fine in reports where the Report PK is mentioned. I also wanted to create a Userprofile dashboard with that AVG, but here it doesn't aggregate wel. Can you help?
The problem is that there is M:N relation between user profile and report which may lead to “double counting”. Report Time to Control may be counted multiple times if you put Report PK+User Profile into your insight because there can be multiple expenses for such a combination. You may create M:N edge from Expense to Report if you do not mind. It will then let you filtr/slice Report dataset by any attributes which can slice/filter Expense. Other option can be to use something like
select avg(select Report Time to Control by Records of expenses)
. It basically means that the value will be included into average for each expense record. It may be also interesting to consider some more complex formula which would calculate weighted average if double counting is an issue.
following formula may also work, but I am not sure what would be semantics:
Copy code
select avg(select avg(Report Time to Control by Report PK, Userprofile)
Thanks Jakub I will try it out
@Jakub Sterba that worked, and I understand why 🙂 Thank you!