Forum Discussion

robertpayne21's avatar
robertpayne21
Frequent Visitor
3 years ago
Solved

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...
@Dax help
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    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's avatar
rubayatyasmin
Icon for Community Champion rankCommunity Champion
3 years ago

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.