Forum Discussion

Frequent Visitor

9 years ago

Solved

Average calculation for distinct orders

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 CA...

Nilrem
9 years ago
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.

Icon for Microsoft Employee rank

Microsoft Employee

9 years ago

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
Frequent Visitor
9 years ago
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?
- v-huizhn-msft
  Microsoft Employee
  9 years ago
  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
    Frequent Visitor
    9 years ago
    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.
- jerry_jerry
  New Member
  3 years ago
  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