Average on distinct values + filter

LisaB ,

Firstly, build a relationship between two tables like below:

Secondly, add an index column in table 'Contract invoicing lines', then create a calculate column using DAX below:

Rank = RANKX(FILTER('Contract invoicing lines', 'Contract invoicing lines'[Contract No] = EARLIER('Contract invoicing lines'[Contract No])), 'Contract invoicing lines'[Index], , ASC, Dense)

Finally, create a slicer based on status column and create a measure in 'Contract invoicing lines' table:

Result = CALCULATE(AVERAGE('Contract invoicing lines'[Line Amount]), FILTER('Contract invoicing lines', 'Contract invoicing lines'[Rank] = 1))

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.