Total in table visualization not being calculated when it depends on row date

[jasonyeung87]

Hi,

I currently have a table visualization that displays timesheet data. It looks similar to:

Each row is a timesheet record for a period of time. I'm trying to obtain the sums of the Vacation for a given time period as well as the total of all the rows. The time period totals are calculated correctly, but the summary total isn't. The measure I'm using is the following:

Sum Time-Off Split Hours Vacation =
COALESCE(
CALCULATE(
SUM('Time-Off Split'[Krow__Hours__c]),
FILTER(
'Time-Off Split',
RELATED('Project Resource'[Project Resource Name]) = SELECTEDVALUE('Project Resource'[Project Resource Name]) &&
'Time-Off Split'[Krow__Date__c] >= SELECTEDVALUE('Timesheet'[Week Start Date]) &&
'Time-Off Split'[Krow__Date__c] < SELECTEDVALUE('Timesheet'[Week End Date]) &&
'Time-Off Split'[IsDeleted] = FALSE &&
RELATED('Time Off'[Krow__Type__c]) = "Vacation"
)
),
0
)

I think it may be because it doesn't know the "date" when generating the total, but I'm not too sure. I was wondering if it's possible to rewrite the measure so that it generates the summary total of all the rows? Thanks!

Jason

[v-tsaipranay · Accepted solution]

Hi @jasonyeung87 ,

Thank you for reaching out to the Microsoft Fabric Community. Also thankyou @pankajnamekar25  for your input.

you're correct, the issue occurs because the Total row lacks row context, so the original measure sums vacation hours across the entire dataset instead of the visible date range.

To fix this, here's a revised measure that:

• Calculates vacation per week as before and dynamically uses the min and max visible dates for the Total row.

Vacation Hours (with Correct Total) :=
VAR IsTotal = NOT HASONEVALUE('Timesheet'[Timesheet ID])
VAR StartDate = MIN('Timesheet'[Week Start Date])
VAR EndDate = MAX('Timesheet'[Week End Date])

RETURN
IF (
    IsTotal,
    CALCULATE(
        SUM('Time-Off Split'[Krow__Hours__c]),
        'Time-Off Split'[Krow__Date__c] >= StartDate,
        'Time-Off Split'[Krow__Date__c] <= EndDate,
        RELATED('Time Off'[Krow__Type__c]) = "Vacation",
        'Time-Off Split'[IsDeleted] = FALSE()
    ),
    CALCULATE(
        SUM('Time-Off Split'[Krow__Hours__c]),
        FILTER (
            'Time-Off Split',
            'Time-Off Split'[Krow__Date__c] >= SELECTEDVALUE('Timesheet'[Week Start Date]) &&
            'Time-Off Split'[Krow__Date__c] <= SELECTEDVALUE('Timesheet'[Week End Date]) &&
            RELATED('Time Off'[Krow__Type__c]) = "Vacation" &&
            'Time-Off Split'[IsDeleted] = FALSE()
        )
    )
)

This should now reflect correct totals based only on the weeks shown in your table.

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[jasonyeung87]

Hi @v-tsaipranay and @pankajnamekar25 ,

Thanks for your help! I tried it, made some minor changes (e.g. wanted to display "0.00" instead of a blank if there is no sum) and it worked!

The final version I used is below; it's almost identical to the one you mentioned.

Sum Time-Off Split Hours Vacation = 
	VAR IsTotal = NOT HASONEVALUE('Timesheet'[Id])
	VAR StartDate = MIN('Timesheet'[Week Start Date])
	VAR EndDate = MAX('Timesheet'[Week End Date])

	RETURN
		COALESCE(
			IF (
				IsTotal,
				CALCULATE(
					//SUM('Time-Off Split'[Krow__Hours__c]),
					//'Time-Off Split'[Krow__Date__c] >= StartDate,
					//'Time-Off Split'[Krow__Date__c] <= EndDate,
					//RELATED('Time Off'[Krow__Type__c]) = "Vacation",
					//'Time-Off Split'[IsDeleted] = FALSE()
					SUM('Time-Off Split'[Krow__Hours__c]),
						FILTER (
						'Time-Off Split',
						'Time-Off Split'[Krow__Date__c] >= StartDate &&
						'Time-Off Split'[Krow__Date__c] <= EndDate &&
						RELATED('Time Off'[Krow__Type__c]) = "Vacation" &&
						'Time-Off Split'[IsDeleted] = FALSE()
					)
				),
				CALCULATE(
					SUM('Time-Off Split'[Krow__Hours__c]),
					FILTER (
						'Time-Off Split',
						'Time-Off Split'[Krow__Date__c] >= SELECTEDVALUE('Timesheet'[Week Start Date]) &&
						'Time-Off Split'[Krow__Date__c] <= SELECTEDVALUE('Timesheet'[Week End Date]) &&
						RELATED('Time Off'[Krow__Type__c]) = "Vacation" &&
						'Time-Off Split'[IsDeleted] = FALSE()
					)
				)
			),
			0
		)

Jason

[pankajnamekar25]

Hello @jasonyeung87 

Try this measure

Vacation Hours (with Total) =

SUMX (

    VALUES('Timesheet'[Timesheet ID]),  // Replace with a unique key per row

    VAR StartDate = CALCULATE(SELECTEDVALUE('Timesheet'[Week Start Date]))

    VAR EndDate = CALCULATE(SELECTEDVALUE('Timesheet'[Week End Date]))

    RETURN

        CALCULATE (

            SUM('Time-Off Split'[Krow__Hours__c]),

            FILTER (

                'Time-Off Split',

                'Time-Off Split'[Krow__Date__c] >= StartDate &&

                'Time-Off Split'[Krow__Date__c] <= EndDate &&

                RELATED('Time Off'[Krow__Type__c]) = "Vacation" &&

                'Time-Off Split'[IsDeleted] = FALSE()

            )

        )

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[jasonyeung87]

Hi @pankajnamekar25 ,

Thanks for your suggestion on the measure. I tried the measure, but it didn't work for me. The result was:

The vacation hours didn't look correct. There was a total generated, but it looks like it was totalling up the vacation hours of all dates. To debug, I created the following measure:

Debug Selected Values =
"WeekStart: " & FORMAT(SELECTEDVALUE('Timesheet'[Week Start Date]), "yyyy-mm-dd") &
", WeekEnd: " & FORMAT(SELECTEDVALUE('Timesheet'[Week End Date]), "yyyy-mm-dd")

And added it to the column. It appears on the right column in above screenshot. It is displaying the start and end dates for each row, but there are no dates for the total row. I wanted the vacation total to be from the start date of the first row and the end date of the last row.

Sincerely,

Jason