Forum Discussion

wes-shen-poal's avatar
wes-shen-poal
Helper III
8 years ago

Create data table containing measures

Hi there,   I would like to create a data table that provides 3 different measures for each day of the week. The table visual below shows what I want the data table to look like.   Day Name...
Need Help
wes-shen-poal's avatar
wes-shen-poal
Helper III
8 years ago

Hi v-caliao-msft

 

Yes that is what I would like ... which is to use that table as a data source. But I would like to use a calculated table method rather than the Export Data method, as I need the numbers to update as new data comes in, i.e. I wouldn't want to export the data every time fresh data comes in (which is on a daily basis).

 

Thanks
Wes

 

 

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi wes-shen-poal

 

Try Summarize function. Add new table from Modelling Tab

MyDataTable =
SUMMARIZE (
    'VMS Calendar',
    'VMS Calendar'[Day Name],
    "Slots Vacated", CALCULATE (
        SUMX (
            VALUES ( 'VMS VesselMovementVehicle'[VehicleID] ),
            CALCULATE ( SUM ( 'VMS VesselMovementVehicle'[Slot Equivalence] ) )
        ),
        USERELATIONSHIP ( 'VMS Calendar'[Date], 'VMS VesselMovementVehicle'[Port Exit Day] )
    ),
    "Date Count", CALCULATE (
        DISTINCTCOUNT ( 'VMS VesselMovementVehicle'[Port Exit Day] ),
        USERELATIONSHIP ( 'VMS Calendar'[Date], 'VMS VesselMovementVehicle'[Port Exit Day] )
    ),
    "Average Slot Vacated", DIVIDE ( [Slots Vacated], [Date Count] )
)





 

  • wes-shen-poal's avatar
    wes-shen-poal
    Helper III
    8 years ago

    Thanks Zubair_Muhammad!! It's very close to achieving what I'm after!!

     

    Is there a way to incorporate a filter into the Table formula, where all the calculations are based on the last 8 calendar weeks of 'VMS VesselMovementVehicle'[Port Exit Day]?

     

    Thank you.

     

    Wes

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi wes-shen-poal

       

      Trying wrapping above code inside CalculateTable i.e.

      MyDataTable =
CALCULATETABLE (
    SUMMARIZE (
        'VMS Calendar',
        'VMS Calendar'[Day Name],
        "Slots Vacated", CALCULATE (
            SUMX (
                VALUES ( 'VMS VesselMovementVehicle'[VehicleID] ),
                CALCULATE ( SUM ( 'VMS VesselMovementVehicle'[Slot Equivalence] ) )
            ),
            USERELATIONSHIP ( 'VMS Calendar'[Date], 'VMS VesselMovementVehicle'[Port Exit Day] )
        ),
        "Date Count", CALCULATE (
            DISTINCTCOUNT ( 'VMS VesselMovementVehicle'[Port Exit Day] ),
            USERELATIONSHIP ( 'VMS Calendar'[Date], 'VMS VesselMovementVehicle'[Port Exit Day] )
        ),
        "Average Slot Vacated", DIVIDE ( [Slots Vacated], [Date Count] )
    ),
    FILTER (
        'VMS VesselMovementVehicle',
        'VMS VesselMovementVehicle'[Port Exit Day] > 44
    )
)