Forum Discussion

Nilrem's avatar
Nilrem
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...
Need Help
  • Nilrem's avatar
    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.

     

v-huizhn-msft's avatar
v-huizhn-msft
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's avatar
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's avatar
    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's avatar
      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.

       

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        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

  • jerry_jerry's avatar
    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