Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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).