Forum Discussion
rsacherry1025
4 years agoFrequent Visitor
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...
- 4 years ago
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.
bcdobbs
Community Champion
4 years agoYou need to iterate over the rows using SUMX.
Can you share a demo pbix file or your data model with some dummy data so can be more specific.
rsacherry1025
4 years agoFrequent Visitor
Hello bcdobbs ,
I will try to create a demo and dummy data.
Thanks.
- rsacherry10254 years agoFrequent Visitor