Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Average of Values in Matrix Visual created by Measure

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_idwork_typecontrol_keywork_centre_sitewodslabacklog_datecarried_newbacklog_date2backlog_Planned/Rectivetotal_scoreeomscore_timecurrent_carried_newSTKprev_scoreScore Changenext_score
ab1c1defghijklmnopq
a1b2c2defghijklmnopq
a2b2c3defghijklmnopq
a3b3c4defghijklmnopq
a4b4c5defghijklmnopq
a5b5c6defghijklmnopq

 

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's avatar
    Anonymous
    5 years ago

    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)])

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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)])