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