I am trying to return an average target but failing to get the correct result. I have created a mock up pbix which demonstrates the issue over a very small amount of data and would really appreciate if someone could see where I am going wrong.
Average Query.pbix (86.1 KB)
- I have a Dates table for 2020.
- I have a Sites table, which contains stores for Aldi and Tescos (made up data!).
- I also have a Targets table, which has granularity of Year-Month. Not all stores have a target. I am returning a target for each month by using TREATAS:
Target RAW =
SUM( Targets[TargetValue] ),
TREATAS ( VALUES ( Dates[YearMonthNo] ), Targets[YearMonthNo] )
I need to return an Average Target, but the calculation needs to exclude the stores where no target exists. My Dax for this is:
Avg Target =
AVERAGEX (Targets,[Target RAW] ),
Targets[TargetValue] <> 0
These are the results I am seeing
Total Target for March 2020 = 5468.
Number of stores with target = 5
Average Target = 1097.20 — Correct.
Total Target for March 2020 = 6601
Number of stores with target = 9
Average Target = 731.60 – Incorrect. It should be 6601/9 = 733.4
Total Target for March 2020 = 12087
Number of stores with target = 14
Average Target = 853.47 – Incorrect. It should be 12087/14 = 863.3
1, I am really puzzled as to why Aldi is fine, but Tesco is incorrect.
2, I have found that if I additionally apply a filter on the targets table of ‘202003’ then the Average results all get corrected. Yet my report is filtered on Month = March and all the RAW target values being pulled through are the correct ones for that month?
Is AverageX the right DAX function for me?