Related calculated column

[lewisbaybutt5DT]

I have three tables in dataverse. A case table, session bookings table and a session types table. My session bookings table has lookups to both the 'case' and 'session type'. My session types table has a column for 'session length' and 'session fee'. How could I create a calculated column to show the total cost of all sessions against a 'case'. I.e. sum of session bookings against their fee. And then a column to show the total duration of sessions i.e. sum of session bookings against session length for a 'case'.

[lewisbaybutt5DT]

 Thank you so much for this Allison. This is perfect!

On another note. I have a 'duration' column in dataverse. Power Query Editor sees it as a number and I try to convert to duration to get it as minutes or to display as hours and minuted but it takes the minutes value and understands it as days. Any ideas?

[AllisonKennedy]

@lewisbaybutt5DT you may need to use math to convert it back to total minutes. I don't use dataverse often so not sure what format it pulls through as. If you can provide sample of the decimal and what duration it should be I can help with the power query M code for it. 

[lewisbaybutt5DT]

Sorry for the slow reply @AllisonKennedy . Here is a couple of screenshots. It is pulling through 1 hour/60 minutes as 60 days.

[AllisonKennedy]

@lewisbaybutt5DT  Maybe transform data type to Text, and Extract > Text Before Delimiter > :

Then Transform Data Type to Decimal number to arrive at total hours?

[lewisbaybutt5DT]

Thanks @AllisonKennedy  - This is a really odd one. I converted to text and then it just displayed as '60' so I couldn't extract. Anyway, I've ended up converting to decimal number and then I used the divide function to divide whatever the value is by 60 to get the hours. It was showing the minutes as 'days', but as a decimal number it doesn't matter as I knew it was minutes so I've gone with that! Thanks for your help 🙂

[AllisonKennedy · Accepted solution]

@lewisbaybutt5DT  Create new measures and put them in a visual with CaseTable[CaseType]: 

Total Cost = SUMX(SessionBookings, RELATED( SessionTypes[SessionLength] ) * RELATED( SessionTypes[SessionLength] ) )

Total Duration = SUMX(SessionBookings, RELATED( SessionTypes[SessionLength] ) )