Create data table containing measures

[wes-shen-poal]

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 is from my 'VMS Calendar' data table

Slots Vacated is...

Slots Vacated = CALCULATE(SUMX(VALUES('VMS VesselMovementVehicle'[VehicleID]),CALCULATE(SUM('VMS VesselMovementVehicle'[Slot Equivalence]))),USERELATIONSHIP('VMS Calendar'[Date], 'VMS VesselMovementVehicle'[Port Exit Day]))

This measure tells me, for a given day of the week, the total number of vehicle slots that were vacated in our yard in a given period, which is determined by my 'VMS Calendar'[Date] filter. e.g. there were 6296 vehicle slots that were vacated on Mondays from our yard.

Date Count is...

Date Count = CALCULATE(DISTINCTCOUNT('VMS VesselMovementVehicle'[Port Exit Day]), USERELATIONSHIP('VMS Calendar'[Date],'VMS VesselMovementVehicle'[Port Exit Day]))

This measure tells me how many Mondays, and Tuesdays...etc in the given period, which is determined by my 'VMS Calendar'[Date] filter. Currently, I have set this Visual level filter to be in the last 8 calendar weeks from the latest 'VMS VesselMovementVehicle'[Port Exit Day]. Hence why we get 8 Mondays, and 8 Tuesdays...etc in the table visual

Average Slot Vacated is...

Average Slot Vacated = DIVIDE([Slots Vacated],[Date Count])

This measure tells me the average number of vehicle slots vacated on a given day of the week. e.g. there were 6296 vehicle slots that were vacated in total across 8 Mondays, so 6296 divide by 8 = 787 vehicle slots gets vacated in our yard on an average Monday.

Currently, these 3 calculated measures mentioned above sits in my 'VMS VesselMovementVehicle' data table.

The relationship between 'VMS VesselMovementVehicle' and 'VMS Calendar', data tables is currenly inactive, hence why in my Slots Vacated and Date Count formulas I use the USERELATIONSHIP() function.

Can someone kindly assist me in turning my table visual to a data table?

Then I can use the Average Slot Vacated numbers from this data table as the forecasted numbers for Slots Vacated measure for future 'VMS Calendar'[Dates]. e.g. the forecasted number of slots vacated on 23rd October 2017, which is a Monday, will be 787 (obtained from this new data table I'm trying to create).

Thank you!
Wes

[v-caliao-msft]

@wes-shen-poal,

Currently, these 3 calculated measures mentioned above sits in my 'VMS VesselMovementVehicle' data table. Can someone kindly assist me in turning my table visual to a data table?

What do you mean by turn tbale visual to a data tbale? Do you want to use this visula as a data table and use it as a datasoure? If that is the case, you can click ellipsis and chose "Export data "

If this is not what you want, please elaborate your requirement, so that we can make further analysis.

Regards,

Charlie Liao

[wes-shen-poal]

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]

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]

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]

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