Forum Discussion

Frequent Visitor

4 years ago

Solved

Incorrect Total showing on the Table

Hi, My goal is to filter all rows with CreateOn dates on or after the Start Date and sum the total effort hours. Below is my table data. I was able to remove those boxed in red by cre...

Need Help

jdbuchanan71
4 years ago
rsacherry1025

The problem you are running into is from what is returned by SELECTEDVALUE(Table1[Convert_StartDate]) on the total row, there it returns a BLANK() so you need to adjust the measure a bit.

We can create a table in the measure to apply as a filter, something like this.

Effort Hours = CALCULATE ( DIVIDE ( SUM ( Table3[Duration] ), 60 ), FILTER ( ADDCOLUMNS ( SUMMARIZE ( Table3, Table3[Ticket] ), "@Start", CALCULATE ( MAX ( Table1[Convert_StartDate] ) ), "@Created", CALCULATE ( MAX ( Table2[Convert_CreatedOn] ) ) ), [@Created] >= [@Start] ) )

That gives me the correct total based on your sample although it may need some tweeking depeding on how your model is layed out.

jdbuchanan71

Super User

4 years ago

rsacherry1025

The problem you are running into is from what is returned by SELECTEDVALUE(Table1[Convert_StartDate]) on the total row, there it returns a BLANK() so you need to adjust the measure a bit.

We can create a table in the measure to apply as a filter, something like this.

Effort Hours = 
CALCULATE (
    DIVIDE ( SUM ( Table3[Duration] ), 60 ),
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Table3, Table3[Ticket] ),
            "@Start", CALCULATE ( MAX ( Table1[Convert_StartDate] ) ),
            "@Created", CALCULATE ( MAX ( Table2[Convert_CreatedOn] ) )
        ),
        [@Created] >= [@Start]
    )
)

That gives me the correct total based on your sample although it may need some tweeking depeding on how your model is layed out.