Forum Discussion

clarkey1988's avatar
clarkey1988
Helper II
5 years ago

Creating a new table from a current table

Hi, I'm wondering if anyone can help.   I have a table in my BI desktop called Trip Detail. I'm trying to create a measure that calulates the average number of trucks used each week in 2021.   Th...
Need Help
parry2k's avatar
parry2k
Super User
5 years ago

clarkey1988 you just need measures, no new table:

 

Avg Revenue = AVERAGE ( Table[Revenue] )

Avg Truck = AVERAGE ( Table[Truck Count] )

Avg Rev Per Truck = DIVIDE ( [Ave Revenue], [Avg Truck] )

 

if this doesn't work, It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.

  • clarkey1988's avatar
    clarkey1988
    Helper II
    5 years ago

    Hi parry2k 

     

    I don't think it's as simple as that. Each truck has a unique truck number.

     

    Currently I'm calculating the distinct count of trucks we've used in 2021 at one point. I need to calculate the average distinct count of trucks for each week in 2021.

     

    Week 2 we used 617

    Week 4 we used 637

    Week 5 we used 642

    Week 6 we used 641

     

    For an average of 634 trucks/week in 2021

    • clarkey1988's avatar
      clarkey1988
      Helper II
      5 years ago
      Distinct Count of Trucks/Week = (CALCULATE(COUNT(DimTractor[TractorAK]),FILTER('Trip Detail','Trip Detail'[Year]=2021)))
       
      Forgot to add my current measure