Need help in creating a calculated column.

[Anonymous]

Hi all. 

In the picture above I have "Exec_Name" and "total" ,"Opportunity Number"and "Opportunity value". The "total" itself is a calculated table which uses the below formula:

revenue measure sample = var a = SUMMARIZE(ATE,ATE[Exec_name],ATE[Opportunity_Number],"total",SUM(ATE[Opportunity_value]))

return

a

For example, in the picture above , "adam branden"(first exec name) has 2 values mapped to him which are 1500.00 and 412502.37. So , I wanted a calulated column as "revenue" which will add this 1500.00+412502.37 that will result as 414,002.37. 

Likewise for every exec name it should add up the 'n' number "opportunity_value" mapped to that exec_name. How do i create this ? Any help would be much appreciated. Thanks in Advance.

Do i require a new calculated column as "revenue" or these adding up of the values can be done in the "total" column itself?

[eliasayy]

Hello can you please try

Calculate([measure of revenue],allexcept(table,table[exc name])) 

[Anonymous]

Hi @eliasayy . The measure of revenue is a calculated table. so when i tried using your formula it is not detecting that calculated table.

[eliasayy]

can you please try this instaed of your main measure?SUMMARIZE(ATE,ATE[Exec_name],ATE[Opportunity_Number],"total",calculate(SUM(ATE[Opportunity_value]),allexcept(table,table[exec name]))

[Anonymous]

@eliasayy Hi. Tried this:

rev = SUMMARIZE(ate,ATE[Exec_name],ATE[Opportunity_Number],"total",CALCULATE(SUM(ATE[Opportunity_value]),ALLEXCEPT(ATE,ATE[Exec_name])))

This is returning an error like this:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[eliasayy · Accepted solution]

Hmm i think your best option would be to first create a calculated column as the formula i gave you first, then do the summary with max

[Anonymous]

@eliasayy okay done