Getting incorrect Average

DAX DAX Calculations
1

Hi all,

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)

Model

  • 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 =
CALCULATE (
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 =
CALCULATE (
AVERAGEX (Targets,[Target RAW] ),
Targets[TargetValue] <> 0
)

These are the results I am seeing

ALDI:
Total Target for March 2020 = 5468.
Number of stores with target = 5
Average Target = 1097.20 — Correct.

Tesco
Total Target for March 2020 = 6601
Number of stores with target = 9
Average Target = 731.60 – Incorrect. It should be 6601/9 = 733.4

All Brands
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?

Help appreciated!

Thanks

Mark

2

@Mark,

Okay, this is tricky but here’s what’s going on:

You have no physical relationship between your date table and your targets table. You’ve correctly used TREATAS to create the virtual relationship in calculating your [Target RAW] measure. However, in your visual, your year slicer and month name slicer are not actually doing anything because there’s no physical relationship present. To test what was going on I created the following diagnostic measure:

Row Count Non Zero Target = 
CALCULATE(
    COUNTROWS( Targets ),
    FILTER(
        Targets,
        [Target RAW] <> 0
    )
)

image
image714×537 22.3 KB

image
image681×523 22.2 KB

You can see from the comparison that when the year month no. fact table slicer has no selection, the calculation picks up an extra non-March 2020 record for Tesco, accounting for the difference between the observed and correct averages.

However, rewriting the DAX measure to incorporate the date table slicer selections and filter the fact table records accordingly produces the correct averages:

Avg Target =

VAR SelYear = SELECTEDVALUE( Dates[Year] )
VAR SelMonth = SELECTEDVALUE( Dates[MonthName] )
VAR SelYrMo =
    CALCULATE(
        SELECTEDVALUE( Dates[YearMonthNo] ),
        FILTER(
            Dates,
            Dates[Year] = SelYear &&
            Dates[MonthName] = SelMonth
        )
    )

VAR AvgCalc =
CALCULATE (   
    AVERAGEX (
        FILTER(
            Targets,
            Targets[TargetValue] <> 0
        ),
        [Target RAW] ),
    FILTER(
        Targets,
        Targets[YearMonthNo] = SelYrMo
    )
)

RETURN
AvgCalc

image

Hope this is helpful. Full solution file attached below.

Note: I originally thought the problem might be with your date table, so I replaced yours with mine (using @sam.mckay’s date table code), so if you stick with your old date table, you’ll need to adapt my DAX to your date field names.

1 Like
3

Hi @BrianJ

Excellent - thank you very much for taking the time to look at this - really appreciated!

Regarding the fixed Avg Target 2, do you actually need to work out variables SelYear and SelMonth? The below seems to work but I wanted to check that I am not missing something in this simplification?!

  Avg Target 3 = 
  CALCULATE (   
  AVERAGEX (
      FILTER(
          Targets,
          Targets[TargetValue] <> 0
      ),
      [Target RAW] ),
  FILTER(
      Targets,
      Targets[YearMonthNo] = SELECTEDVALUE( Dates[YearMonthNo] )
  )
)

Thanks

Mark

4

@Mark,

No, that construct works fine too since the year and month slicers together determine the selected value for YearMonthNo. I just explicitly linked my measure to the date table slicers you had on your page to try to make the solution as clear as possible.

  • Brian
1 Like