topic Re: Need help in creating a calculated column. in DAX Commands and Tips

Need help in creating a calculated column.

Anonymous — Tue, 25 Oct 2022 13:35:50 GMT

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

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?

Re: Need help in creating a calculated column.

Anonymous — Tue, 25 Oct 2022 13:44:02 GMT

Hello can you please try

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

Re: Need help in creating a calculated column.

Anonymous — Tue, 25 Oct 2022 13:51:26 GMT

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

Re: Need help in creating a calculated column.

Anonymous — Tue, 25 Oct 2022 15:27:37 GMT

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

Re: Need help in creating a calculated column.

Anonymous — Tue, 25 Oct 2022 16:37:17 GMT

Anonymous 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.

Re: Need help in creating a calculated column.

Anonymous — Tue, 25 Oct 2022 17:28:53 GMT

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

Re: Need help in creating a calculated column.

Anonymous — Tue, 25 Oct 2022 18:47:39 GMT

Anonymous okay done