cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Average a measure returning a single max value

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

1 ACCEPTED SOLUTION
Super User

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 👍

Proud to be a Super User!

5 REPLIES 5
Frequent Visitor

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.

Super User

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 👍

Proud to be a Super User!

Frequent Visitor

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

Super User

Happy to help. Will appreciate some kudos. 👍

Proud to be a Super User!

Super User

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.

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors