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...
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] ) )
)

     

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
    Icon for Community Champion rankCommunity 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.

       

       

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

        Anonymous 
        Thank you for your reply. Again you are right about the error in the code. I noticed that after I read your first comment. We can simply iterate over the projects table

         

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

         

        This will give the same result of your measure in case no duplicates. In case of duplicate project no. your measure sums the spendings and sums the margins then performs the multiplication which I don't know if it has any meaning. It would make sense if there is a requirement to select the MAX or MIN among both the margin and the spending using MAX/MIN instead of SUM. Otherwise I see no reason for complicating the code.
        My approach was based on the assumption that one project has one spending and target which makes complete sense. Therefore this code shall have no issues

         

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

         



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