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

     

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

      tamerj1 , Anonymous 

       

      Let me explain more in details. 

      • Profit target used to be a conditional column in power query table "dProject", but now I moved it as a measure in the same "dProject". As you can see, this % is manually set because we decide arbritarely how much profit we want. And yes, there are no redundant project codes they are all unique, but they can have the same target. see bellow: 

      can we have a correct calculated total even if the measure itself is manual? 

      •  Income target is cirrectly calculated based on the profit target but not the total.

      Let me dig into detail in you proposals 

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        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] ) )
)