Average of Values in Matrix Visual created by Measure

[Anonymous]

Hi everyone,

I have a matrix visual which is populated by a measure which simply calculates the difference between a sum of data in one month and the previous month:

The measure is calculated as follows:

Backlog Change (from prior month) = [backlog_score_TM]-[backlog_score_LM]

Based on the following 2 measures:

backlog_score_LM =

var lm = MAX(ag_wrap_output_monthly[backlog_date2]) -- this is the previous month field

return

CALCULATE(SUM(ag_wrap_output_monthly[total_score]),ag_wrap_output_monthly[backlog_date]=lm) + 0

backlog_score_TM =

var tm = MAX(ag_wrap_output_monthly[backlog_date])

return

CALCULATE(SUM(ag_wrap_output_monthly[total_score]),ag_wrap_output_monthly[backlog_date]=tm) + 0

The measure is evaluated across several months in the visual and behaves as expected.

What I want is to average these values across the periods (which might be filtered by a slicer).

So I would expect the average for London to be: -2899 and Thames Valley to be: -1266

The source data is organised as follows where backlog date is always the first of the month and backlog_date 2 is always the previous month (also first of the month):

operation_id

work_type

control_key

work_centre_site

wodsla

backlog_date

carried_new

backlog_date2

backlog_Planned/Rective

total_score

eom

score_time

current_carried_new

STK

prev_score

Score Change

next_score

a

b1

c1

d

e

f

g

h

i

j

k

l

m

n

o

p

q

a1

b2

c2

d

e

f

g

h

i

j

k

l

m

n

o

p

q

a2

b2

c3

d

e

f

g

h

i

j

k

l

m

n

o

p

q

a3

b3

c4

d

e

f

g

h

i

j

k

l

m

n

o

p

q

a4

b4

c5

d

e

f

g

h

i

j

k

l

m

n

o

p

q

a5

b5

c6

d

e

f

g

h

i

j

k

l

m

n

o

p

q

I have created the following measure to attempt to average the values to find the "typical month on month change":

AverageTest = SUM(ag_wrap_output_monthly[Score Change])/DISTINCTCOUNTNOBLANK(ag_wrap_output_monthly[backlog_date])

but it does not produce the values as above.

Please could somebody assist with what I'm doing wrong? 🙂 

[Anonymous · Accepted solution]

Solved with the following DAX:

AvgScore = AverageX(SUMMARIZE(ag_wrap_output_monthly,ag_wrap_output_monthly[backlog_date],"Measure",[Backlog Change (from prior month)]),[Backlog Change (from prior month)])