3 Replies Latest reply: Dec 7, 2016 4:59 AM by Gysbert Wassenaar

# Need help in calculation

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.

• ###### Re: Need help in calculation

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.

• ###### Re: Need help in calculation

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.

• ###### Re: Need help in calculation

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).