Need help in calculation

[spavankumar1980]

Hello All,

I have a pivot table where my

1.  "Column 1" dimension in Rows

2.  "Quarter"  dimension in Column. This changes dynamically based the date selected.It will  always display 3 quarters.

          if(Date >='$(vRolling3QtrStartDate)' and Date<= '$vRolling3QtrEndDate)', Quarter)

3.    Measure is an calculation =  Sum(X)/Sum(total(X).

	Q1	Q2	Q3
XYZ1	2%	1%	2%
XYZ2	5%	7%	6%
XYZ3	10%	8%	8%
XYZ4	3%	2%	5%

I need some help in calculating my measure,  where i  can display the measure as a Weighted Average of all the 3 months that falls under particular quarter. Say suppose if I select a date 11/25/2016. I will display 3 completed quarters as Q1, Q2, Q3 of 2016.

and i want the measure for Q1 and XYZ1 combination as weighted average of all the 3 months that fall under that Q1.

Any ideas or suggestions? I hope i haven't confused.

[Gysbert_Wassenaar]

Perhaps this: avg(aggr(Sum(X)/Sum(total X),Month, [Column 1]).

Make sure to use the correct field names; Month and [Column 1] are just examples based on what you posted.

[spavankumar1980]

Thanks for that.

However there is a some change in the requirement itself now. Instead of displaying the weighted average of the measure for the months that are falling under that particular quarter, now  i need to display the measure value only for the last month of that particular quarter. Ex: In Q1 i need to display the value of March, for Q2  it is June, Q3 it is September and Q4  it is December. Any ideas.

[Gysbert_Wassenaar]

You could filter the months with a bit of set analysis if you have a numeric month field. sum({<Month={"=Mod(Month,3)=0"}>}X).