Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
robertpayne21
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
 
robertpayne21_1-1689778258166.png

 

 
robertpayne21_0-1689778071695.png

 

 

1 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 👍

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

5 REPLIES 5
robertpayne21
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. 

robertpayne21_0-1689782642349.png

 

robertpayne21_1-1689782691033.png

 

 

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.

 



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 👍

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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

Happy to help. Will appreciate some kudos. 👍

 

 


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


rubayatyasmin
Super User
Super User

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.

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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