Pivottable % of parent row running weighted average

[Anonymous]

Hi, 

I have a pivot table with % of parent row total calculated via Value field settings in the pivottable.

I want to calculate the weighted average of the % of parent row total over different category A and B by: 

1. Calculating the rolling average of % of parent row total for the actual week, the past week, and the coming week, for category A and B. Let's call it result X for category A and result Y for category B

2. Calculating the average of X and Y, where X is given weight 1 and Y weight 0.5

Any ideas how that can be done much appreciated! 

[Anonymous]

Hi @Anonymous 

From your post, I think you wan to calculate the rolling average for actual week, the past week, and the coming week(3 weeks)

Due to I don't know your data source, I build a sample to have a test.

My Sample:

My measure:

Rolling Avg A = AVERAGEX(FILTER(ALL('Table'),'Table'[WeekNum]<=MAX('Table'[WeekNum])+1&&'Table'[WeekNum]>=MAX('Table'[WeekNum])-1),'Table'[A])

Rolling Avg B = AVERAGEX(FILTER(ALL('Table'),'Table'[WeekNum]<=MAX('Table'[WeekNum])+1&&'Table'[WeekNum]>=MAX('Table'[WeekNum])-1),'Table'[B])

Result:

You see Rolling Avg A in WeekNum = (2+3+10)/3.

I am confused about your require 2. What does "X is given weight 1 and Y weight 0.5" mean?

If this reply still couldn't help you solve your problem, please show more details about your table, your calculate logic to me.

Or you can share your pbix file with me by your Onedrive for Business.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi @Anonymous ,

Perfect that solves the first part of the question.

Sorry for being unclear about the second part, what I mean is that I want the average of A and B for each week, but A is given weight 1 and B is given weight 0.5, for example for week 21 I want (5*1 + 1*0.5)/(1.5) =3.67. Would be great if you have a solution for that! 

[Anonymous · Accepted solution]

Hi @Anonymous 

You can try this measure:

Avg of A and B = DIVIDE([Rolling Avg A]*1+[Rolling Avg B]*0.5,1.5)

Result is as below.

Week 21 return to (5*1 + 1*0.5)/(1.5) =3.67.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Thanks!

[Ashish_Mathur]

Hi,

It's an MS Excel question.  Post it here - Results in Microsoft 365 and Office - Microsoft Community 

[Anonymous]

Hi

Ok thanks for redirecting. However, would it be possible to achieve this in PowerBI? 

[Ashish_Mathur]

Hi,

Since you have weeks involved here and weeks spill into following months, I find it difficult to use Date/Time Intelligence functions where weeks are involved.  So I am sure, someone else will be able to help you here.