Average a measure returning a single max value

[robertpayne21]

Hi,

I have a measure that is returning the max price value for a customer.  This would be their most recent price level based on the selected date.  I would like to average this.  

Measure to return most recent price entry:

Hmmm = max(Price_Table[LABOR_RATE])

Measure to return max based on date:

_TEST1 Dated Avg Labor Rate =

var _minsel = MAX(DimDate[Date])

var _selected = LASTNONBLANKVALUE(FILTER(ALL(DimDate[Date]), DimDate[Date]<=_minsel),[Hmmm])

Return _selected

Measure to return the average of those values.

AVG Of Max based on date = AVERAGEX(VALUES(DimDate[Date]), [_TEST1 Dated Avg Labor Rate])

The "average" is returning an incorrect value.  The average should be 152.  Again, the row values are correct.  However, the "Total" value is not, it is returning 139 vs the expected 152

[robertpayne21]

Hi @rubayatyasmin , this works if I pick the specific date for the price entry.  For example, if pricing is done on 4/1/2023, the measures return the expected result and the average is correct.  If I pick 4/2/2023, the data is blank.  If I swith to MonthYear, picking 04/2023 returns the value.  If I pick 05/2023 the data is blank.  I'm looking to return that price value as long as there is not a new price record more recent that <= currentdate. 

Latest Price =

CALCULATE(

MAX(Price_Table[LABOR_RATE]),

FILTER(

DimDate,

DimDate[Date] <= MAX(Price_Table[SELECTION_DATE_START]

)

))

----

Average Latest Price =

AVERAGEX(

VALUES(Price_Table[CUSTOMER_NUMBER]),

[Latest Price]

)

Again, this is working as long as I choose the date the price record was entered.

[rubayatyasmin · Accepted solution]

In your current DAX setup, the logic seems correct, but there may be an issue with the way the dates are being handled. If you select a date where there are no price records, the measure will return blank. If you select a date after a price record, it should still return the most recent price record.

The problem may be related to the relationship between your DimDate and Price_Table tables, or how the Price_Table[SELECTION_DATE_START] is being used in the calculation.

Here's a slightly modified version of your DAX measure which should handle dates correctly:

Latest Price =
CALCULATE(
MAX(Price_Table[LABOR_RATE]),
FILTER(
ALL(Price_Table[SELECTION_DATE_START]),
Price_Table[SELECTION_DATE_START] <= MAX(DimDate[Date])
)
)

Average Latest Price =
AVERAGEX(
VALUES(Price_Table[CUSTOMER_NUMBER]),
[Latest Price]
)

Ensure the data type of the SELECTION_DATE_START field is date. If the date field is text, it might lead to the wrong results due to lexicographic comparison rather than date comparison.

also validate that no other filter is applied and validate the relationship between dimdate and price table. 

If my assistance helped you in any way, hit 👍

[robertpayne21]

@rubayatyasmin , thank you so much.  That worked.  Again, thank you.

[rubayatyasmin]

Happy to help. Will appreciate some kudos. 👍

[rubayatyasmin]

Hi, @robertpayne21 

The issue might be stemming from the AVERAGEX function as it is evaluating the expression for each row of the table, and then taking the average of the results. In other words, it's calculating the average of your measure values across all different dates, not the average of unique/latest rates per customer.

Here's an alternative way to create the measures using PowerBI's DAX.

First, let's calculate the latest price per customer:

Latest Price =
CALCULATE(
MAX(Price_Table[LABOR_RATE]),
FILTER(
Price_Table,
Price_Table[Date] = MAX(Price_Table[Date])
)
)

Then, let's calculate the average of the latest price per customer:

Average Latest Price =
AVERAGEX(
VALUES(Price_Table[Customer_ID]),
[Latest Price]
)

The above assumes you have a 'Customer_ID' column to identify unique customers.

This will first get the latest price per customer and then calculate the average of these latest prices.

If you don't have a unique identifier for customers, you would need to add that to your data model.