Forum Discussion
Average on distinct values + filter
LisaB ,
Why does the Item No 101 and 201 not included? Could you please clarify more logic about this?
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.
Hi v-yuta-msft,
The value I want to calculate is the average ARR. Items 101 and 201 are not included in the defintion of the ARR. Hope this clarifies.
Thanks.
L
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.
Hi v-yuta-msft ,
Thanks. Could you, if possible, please explain the functionality of index and what the rank formula does?