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
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.]))

 

 

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.

     

6 Replies

  • 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