Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
wes-shen-poal
Helper III
Helper III

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.

Capture.PNG

 

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.

 

Picture1.png

 

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

5 REPLIES 5
v-caliao-msft
Microsoft Employee
Microsoft Employee

@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 "

Capture.PNG

 

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

 

Regards,

Charlie Liao

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

 

 

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





 


Regards
Zubair

Please try my custom visuals

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

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

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.