I have been having a heck of a time getting this to work so I am throwing in the towel and reaching out. I have a VERY simple problem that seems to have a rather complex solution.
Essentially I am trying to take an average value of a measure for a certain date range. The original measure is a IF/ThEN Statement that takes a value and puts it into a % bucket... Below is a sample data set. All columns are measures (except week) and given the way the data and calculations work I cannot add a calculated column to solve this issue.
Week Ending Value Tier BI-Weekly Average of Tier
Week 1 .145 90% -
Week 2 .245 80% 85%
Week 3 .089 100% -
Week 4 .345 70% 80%
Etc. for 14 weeks, but 7 BI-Weekly values that need averaging.
Of this sample data the number I am looking for is Average of Bi-Weekly Tiers "Quarterly %= 82.5%" (I know there is a flaw in the math logic by taking an average of an average, but that is my directive). The formala I am using is:
Quarterly %:= CALCULATE(AVERAGEX(VALUES('Report Date'[Week Ending]), [BI-Weekly Average of Tier]))
Every measure in the caluclation series works properly, however, when I use the "Quartly %" formula to get the final average of the "BI-Weekly Average of Tier" I get varrying results for different 'Business Location'. For 50% of the locations (34) the restults are perfect. However for the other 50% it doesnt work. Each location is setup as a drop down. For the 'problem' locations I get a rounded number like 80.00% and its close, but not exact like 82.5%.
Other formulas I have tried are:
Quarterly %:= CALCULATE(AVERAGEX(VALUES('Report Date'[Week Ending]), [BI-Weekly Average of Tier]),
FILTER(VALUES('Report Date'[Week Ending]),ISEVEN([Week Ending])))
Quarterly %:= IF(ISEVEN([Week Ending]),CALCULATE(SUMX(VALUES('Report Date'[Week Ending]), DIVIDE([BI-Weekly Average of Tier],7))),0)
I tried GEOMEANX as well and it came closer but no cigar
Thank you in advance for your help with this!
