Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Hi @bigman24 ,
Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |