Forum Discussion
Related calculated column
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 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] ) )
6 Replies
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] ) )
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?
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.
Sorry for the slow reply AllisonKennedy . Here is a couple of screenshots. It is pulling through 1 hour/60 minutes as 60 days.
