Forum Discussion

rsacherry1025's avatar
rsacherry1025
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 creating this measure.

 

 

 

However, I noticed that the sum total of effort hours is not correct.

I hope someone can give me some advice if something is wrong or missing in my measure. 

 

Thank you in advance.

 

 

 

Need Help
  • jdbuchanan71's avatar
    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.

     

     

8 Replies

  • rsacherry1025's avatar
    rsacherry1025
    Frequent Visitor
    4 years ago

    Hello jdbuchanan71 , bcdobbs , Anonymous ,

    This issue is already resolved.

    I need to mix all your suggestions to make work. I used SUMX and tweak my model.

    Thanks for all the help.

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    You 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.

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper 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.

     

     

    • rsacherry1025's avatar
      rsacherry1025
      Frequent Visitor
      4 years ago

      Hello jdbuchanan71 ,

      Thank you I will try to update my measure based on your reply.
      I will update you on the status.

      Thank you.

    • rsacherry1025's avatar
      rsacherry1025
      Frequent Visitor
      4 years ago

      Hello jdbuchanan71 ,

      Here's my update after applying your suggestion.

       

      Kind of weird because the total below is not appearing.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  rsacherry1025 ,

    You can create a measure and place it between the visual and the [effort Hrs] for comparison:

    Calculate Sum =
var Table1=SUMMARIZE('Table','Table'[Start Date],"_value",[effort Hrs])
return IF(HASONEVALUE('Table'[Start Date]), [effort Hrs],SUMX(Table1,[_value]))

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.