CODEWITHSUNDEEP
daxaveragepowerbi-desktop
jcoke
jcoke

Reputation: 1891

Averagex not producing the correct total

I have created a DAX measure which gets the previous period value which is correct on a row by row basis, however because the context i'm using it in is non-additive it causes the grand total to be incorrect.

The solution would be to create another DAX measure which creates a summary table to actually look at the exact individual values and get the average of those numbers but the total is still yielding an incorrect result.

testMeasure = 
VAR _summary = 
    ADDCOLUMNS (
        SUMMARIZE ( 
        'Calendar', 'Calendar'[weekNumWeekCommence]),
        "bays",
        [AvgBaysPreviousYear]
    )
RETURN
AVERAGEX(_summary, [bays])

enter image description here

The average should show as 7,714.24

PBI file: https://drive.google.com/file/d/1GQslMEqdntvcH4Th6kESm8EaIYJN-QwM/view?usp=drive_link

Upvotes: 1

Views: 388

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

Issue

totalAverageBays = 
    AVERAGEX (
        SUMMARIZE('Campaign Overview', 'Calendar'[weekNumWeekCommence], "bays", [totalAverageBaysFinalVersion])
        ,[bays]
    )

AvgBaysPreviousYear = 
IF (
    ISFILTERED('Calendar'[weekNumWeekCommence]) || ISFILTERED('Calendar'[Calendar Financial Year]),
    CALCULATE ( 
        [totalAverageBays],
        SAMEPERIODLASTYEAR('Calendar'[Calendar Date])
    )
)

You have the above two measures, where totalAverageBays is doing a weekly average based on week commencing, but then AvgBaysPreviousYear is asking totalAverageBays to do the same for the same dates as last year. And last year in the same date range, it happens to overlap two weeks. And in one of those weeks it was 0 so you're getting the average of 0 and 7,714.24.

Solution
What you need to do is get the same week number as last year and get the calculation on that.

Luckily, your Calendar table has Year & Week and YOY Year & Week columns which we can leverage.

First, let's clean up totalAverageBays (doing the same thing):

totalAverageBays = 
  AVERAGEX (
    DISTINCT('Calendar'[weekNumWeekCommence],
    [totalAverageBaysFinalVersion]
  )

Now let's tackle the YoY for the same week numbers.

AvgBaysPreviousYear = 
  var lastYearWeeks = CALCULATETABLE(DISTINCT('Calendar'[YOY Year & Week]))
  var result = 
    CALCULATE(
      [totalAverageBays],
      REMOVEFILTERS('Calendar'),
      'Calendar'[Year & Week] IN lastYearWeeks
    )
  return
    IF(
      ISFILTERED('Calendar'[weekNumWeekCommence]) || ISFILTERED('Calendar'[Calendar Financial Year]),
      result
    )

We are getting all in scope YOY Year & Week values, then REMOVEFILTERS on the Calendar so we get all rows, then filtering on Year & Week based on YOY Year & Week.

Upvotes: 1

Related Questions