Average calculation for distinct orders

[Nilrem]

How can I calculate average to take only once an order number [Transport No.]?

Example:

Transport No.    MAX PCT
AAA                   80
BCA                   75
AAA                   80
CAD                  60
BCA                  75
GHA                 68
AGH                 77
AAA                 80

I would like to filter out from the calculation the redundant order numbers (use them onec only for the calculation). The following doesn't work:

Avarage Max PCT = CALCULATE(AVERAGE(SLF_MAIN[MAX PCT]);DISTINCT(SLF_MAIN[Transport No.]))

[v-huizhn-msft]

Hi @Nilrem,

You can right click your table->Edit Quey, right column header->Remove Duplicates, you will get distinct row table as follows.

Then you create a meaasure to get the expected result.

Avarage Max PCT = Average(Table[Max PCT])

Best Regards,
Angelia

[Nilrem]

Thank you for your answer. Unfortunately I have to keep that records. How can I create an other table automaticaly which will contain only unique records?

[jerry_jerry]

Hi

Assume that you need to keep the original table without creating a new one. 

What would be the correct filter to apply on column Transport No. ?

thanks

[v-huizhn-msft]

Hi @Nilrem,

Please create a new table by clicking "New Table" under Modeling on home page. Please type the following formula, you will get distinct table.

NewTable = DISTINCT(Table1)

Then calculate the average based on the new table.

Best Regards,
Angelia

[Nilrem · Accepted solution]

Hi Angelia,

Thank you for your hints. I think the solution will be:

1) Query editor -> Manage -> Duplicate

2)Right click on the column in the new table -> Remove duplicates

I think it will be automatic solution.

[v-huizhn-msft]

Hi @Nilrem,

Congratulations, you have resolved your issue by yourself. Please mark your solution as answer, so that more people will find the workaround easily. 

Thanks,
Angelia