Customize table totals

[YannLG]

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.

[v-rzhou-msft]

Hi @YannLG ,

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.

[YannLG]

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.  

[tamerj1]

@v-rzhou-msft 

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!

[v-rzhou-msft]

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]

@v-rzhou-msft 
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] ) )
)

[YannLG]

@tamerj1 , @v-rzhou-msft 

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 · Accepted solution]

@YannLG 

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]

HI @YannLG 
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]
)

[YannLG]

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.