adjust an average

[jjasper]

Hello,

I am not sure if I am going to explain this correctly or not, but I am looking for a way to adjust a rolled up average. My current graph has a drill down/drill up. When you drill up it gives the overall average for each domain. The domains are made out of questions. Each average is for each question across 19 sites. When rolled up into the domains it is taking the average of the questions that fall under that domain. My issue is the first two years of the data there was only 4 questions for a specific domain but the last two years we have added 3 extra questions and now for the previous two year (2016,2017) the average is not calculating correctly because it is going off the additional questions added. I am not sure how to fix this. I need 2016, 2017 to be divided by 4 questions for the average and 2018,2019 to be divided by 7 for the average.

I really hope this makes sense.

TIA

[v-yuta-msft]

@jjasper ,

Could you share more details about your requirement and give some sample data?

Regards,

Jimmy Tao

[jjasper]

@v-yuta-msft 

I have a table that has data that has Village, Domain, Question and Average. So each questions falls under a domain and then those domains are rolled up via PowerBi. What is currently happening is we switched to a new survey in 2018 and now there is more questions added to a specific domain. What it looks like Power Bi is doing is it is taking all the questions in that domain and averaging them. But for 2016 and 2017 there is only 4 questions in the domain vs. 2018 and 2019 where there is 7 questions in the domain. 

What I would like to do is for 2016 and 2017 average the domain average by only 4 qeustions and for 2018 and 2019 average the domain by 7 questions. When I roll up to the domains it is throwing my averages off because it is dividing by more questions then it needs to.