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

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