Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Customize table totals

Hi!

I have a table with manual entry fields and calculated fields based on other fields. The totals are reflecting the field calulation ginving the wrong result. I explained why I need in the bellow 2 pictures. Thank you for your help in advance.

 

 

 

 

table totals
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 

    You mean to say that the target is a hard coded calculated column. I'm I right?

    then the Income target measure would be

    Income target =
SUMX (
    Projects,
    ( Projects[Profit target] + 1 ) * Projects[Spendings]
)

    And the profit would be

    Profit Target Measure =
IF (
    HASONEVALUE ( Projects[Project No.] ),
    VALUES ( Projects[Profit target] ),
    DIVIDE ( [Income target], SUM ( Projects[Spendings] ) )
)

     

9 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    HI Anonymous 
    You try 

    Profit Target Measure =
IF (
    HASONEVALUE ( Projects[Project No.] ),
    VALUES ( Projects[Profit target] ),
    DIVIDE ( [Income target], SUM ( Projects[Spendings] ) )
)
    Income target =
SUMX (
    VALUES ( Projects[Project No.] ),
    Projects[Profit target] * Projects[Spendings]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      thanks for you help. I actually moved the hardcoded in columns to measure, but i figured out how to customize your proposal.

      Thanks again fo you help.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    I have a test tamerj1's code, and I think there should be something wrong in it. If your table have duplicate Project No, values function will cause issue. [Income target] measure will show error as well.

    You can try my code as below.

    Income target = 
VAR _SUMMARIZE =
    SUMMARIZE (
        Projects,
        Projects[Project No.],
        "Profit target", CALCULATE ( SUM ( Projects[Profit target] ) ),
        "Spending", CALCULATE ( SUM ( Projects[Spending] ) )
    )
VAR _Add_Income_target =
    ADDCOLUMNS ( _SUMMARIZE, "Income target", ( [Profit target] + 1 ) * [Spending] )
RETURN
    SUMX ( _Add_Income_target, [Income target] )
    M Profit target = 
VAR _Spending =  SUM(Projects[Spending])
VAR _Profit_target = SUM(Projects[Profit target])
Return
IF (
    HASONEVALUE ( Projects[Project No.] ),
    _Profit_target,
    DIVIDE ([Income target]  , _Spending) -1
)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      Anonymous 

      Thats correct. But what does it mean to have two targets for one project? It means one thing: there is something wrong with data and need to be fixed. In rhis case my code will detect the problem while your sophisticated code will duplicate the income target. Thank you and have a great day!

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi tamerj1 ,

         

        If your [Income target] is a measure, it will show error because values in filter field.

        If [Income target] is a calculated column, I think it will show incorrect result as below. We can see that Project target will show result as [Project target]*10 due to SUMX, so [Project target]*[Spending] is incorrect.

        Then in [Profit Target Measure] , I think values function will return a column table, I think sum or selectedvalue should be better.

         

        Best Regards,
        Rico Zhou

         

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

         

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for your feedback. Amazing you recreated the table! This is a bit complicated for me so let me look into it before closing the request.