Forum Discussion

mumair's avatar
mumair
Helper I
9 years ago
Solved

How to Segment Data

So let's say I have 40 rows of data. Each row has one location, and there are 5 total possibilities. There is also a time to complete a task associated with each row. How can I show the average time to complete the task segmented by location?

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi mumair,

    Please create measure using the formula below.

    Average = AVERAGEX( DISTINCT(SELECTCOLUMNS(Table2,"Train",Table2[Train],"Location",Table2[Location],"Time",Table2[Time to Complete Inspection])),[Time])


    Create a table visual to display the result.



    Thanks,
    Angelia

4 Replies

  • mumair's avatar
    mumair
    Helper I
    9 years ago

    Actually I have another complication. Say my data looks like this:

     

    Shipment | Train | Location | Time to Complete Inspection

    Order 1 | Train A | New York | 60

    Order 2 | Train A | New York | 60

    Order 3 | Train B | New York | 30

    Order 4 | Train C | Chicago | 45

    Order 5 | Train C | Chicago | 45

     

    I want to know the average time to complete inspection per location. The time to complete inspection is related to the train, and not the shipment. Right now my calculations say it takes an average of 50 minutes to complete inspections at New York. This is incorrect. It should say it takes an average of 45 minutes to complete an inspection. This is because there are only 2 inspections at New York, Train A and Train B, not 3. How do I fix this?

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

      Hi mumair,

      Please create measure using the formula below.

      Average = AVERAGEX( DISTINCT(SELECTCOLUMNS(Table2,"Train",Table2[Train],"Location",Table2[Location],"Time",Table2[Time to Complete Inspection])),[Time])


      Create a table visual to display the result.



      Thanks,
      Angelia