How to calculate average value of running max values each day?

[Anonymous]

hello

• I calculated the max running for my data as per the dax  formula - see below

 

dax and model

 

 

• my actual result is following

 

actual result

 

 

• i want to have the average of each day in total field

 aimed result

 

• this is my data table 

 

data table

 

• this is my date table

 

data table

 

 

thanks a lot

[Anonymous]

Hi @MarkLaf 

thanks a lot for your response,

it's exactly what i needed for plan% values. [first formula]

and for the actuals % values i will use your sencond formula, since i need the max actual values to show up along with data line.

thanks again @DataNinja777 and @MarkLaf 

[MarkLaf · Accepted solution]

Try the below.

Running Max Avg = 
VAR _curDate = MAX(CalendarData[DateID]) RETURN
CALCULATE(
    AVERAGEX(
        VALUES(Schedules_Progress[Item]),
        CALCULATE(MAX(Schedules_Progress[Progress]))
    ),
    FILTER(
        ALL(CalendarData[DateID]),
        CalendarData[DateID] <= _curDate
    ),
    Schedules_Progress[Type] = "Plan"
)

The main change here is that we are doing AVERAGEX over Schedules_Progress[Item]: when evaluated within the matrix, the Item columns will ensure that we are just evaluating over a single Item, and then in the Total column we will evaluate over all Item's in the broader filter.

 
Also, given my personal experience of thinking you are at actual % complete of 70% and then by next week reaslizing that actually we are only 50% done (i.e., can be a wrong assumption that the max % complete will always be the most up to date), I tried out a measure that grabs the last % complete per item for the average.

Running Last Avg = 
VAR _curDate = MAX(CalendarData[DateID])
RETURN
CALCULATE(
	AVERAGEX(
		VALUES(Schedules_Progress[Item]),
		VAR _lastItem = INDEX( 
            1, 
            CALCULATETABLE(DISTINCT(Schedules_Progress)), 
            ORDERBY( Schedules_Progress[Date], DESC ) 
        )
        RETURN
		    CALCULATE( VALUES(Schedules_Progress[Progress]), _lastItem )
	),
	FILTER( ALL(CalendarData[DateID]), CalendarData[DateID] <= _curDate ),
	Schedules_Progress[Type] = "Plan"
)

Output - this is a snip of my test data at top with the two above measures together in a matrix below. Highlighting 5 Feb to show difference in how they behave.

[Anonymous]

hi @DataNinja777 

thanks a lot for your quick interaction,

but i could't figure out the relationship between the two formulas you've provided, since there no mention of 

Running Max %

in 

Avg Running Max %

thanks to clarify this point

[DataNinja777]

Hi @Anonymous ,

To calculate the average of the running max values per day in Power BI, you first need a measure that determines the Running Max for each day. The existing measure calculates the highest progress percentage recorded for each day.

Running Max % = 
CALCULATE(
    MAX(Schedules_Progress[Progress]),
    FILTER(
        ALL(CalendarData),
        CalendarData[DateID] <= MAX(CalendarData[DateID])
    ),
    Schedules_Progress[Type] = "Plan"
)

Once you have the running max values, the next step is to compute the average of these max values per day and display it in the Total column. This can be achieved by summarizing the data per day, extracting the maximum progress for each date, and then calculating the average of these values.

Avg Running Max % =
VAR MaxValuesPerDay =
    SUMMARIZE(
        CalendarData,
        CalendarData[DateID],
        "MaxProgress", MAXX(
            FILTER(
                Schedules_Progress, 
                Schedules_Progress[Type] = "Plan"
            ),
            Schedules_Progress[Progress]
        )
    )
RETURN
AVERAGEX(MaxValuesPerDay, [MaxProgress])

This formula first groups the data by DateID, then calculates the max progress for "Plan" type on each day, and finally takes the average of all the daily max values. The expected outcome is that the Total column in your matrix visual will reflect the average of the daily max progress values, aligning with the aimed result in your screenshot.

Best regards,