topic Re: Need help in calculation in App Development

Need help in calculation

spavankumar1980 — Fri, 02 Dec 2016 21:32:48 GMT

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

Gysbert_Wassenaar — Sun, 04 Dec 2016 10:05:03 GMT

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

spavankumar1980 — Tue, 06 Dec 2016 14:33:09 GMT

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

Gysbert_Wassenaar — Wed, 07 Dec 2016 09:59:32 GMT

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