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

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