Need Help with Calculated Column which uses "SUMMARIZE"

[Anonymous]

Hi all . I am facing an error when ever I am trying to create a new calculated column. 

The calculated Column is :

revenue measure ate = var a = CALCULATETABLE(SUMMARIZE(ATE,ATE[ATE_GATE_Name],"Total",SUM(ATE[Opportunity_value])))

return

GROUPBY(a,ATE[ATE_GATE_Name],[Total])

I am facing an issue like:

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

What is the work around/solution of this error? Any help would be much appreciated. Thanks in Advance!

[Anonymous]

.

[v-easonf-msft]

Hi， @Anonymous 

Please check if below formula could help.

Revenue =
CALCULATE (
    SUM ( ATE[Opportunity_value] ),
    ALLEXCEPT ( ATE, ATE[ATE_GATE_Name] )
)

If it doesn't work, please share a sample file for further research.

Best Regards,
Community Support Team _ Eason

[Anonymous]

.

[v-easonf-msft]

Hi, @Anonymous 

Have you checked the summarize type of this calculated column?

Also, try converting it to a measure or use the following formula if it works.

Revenue =
SUMX ( VALUES ( ATE[ATE_GATE_Name] ), ATE[Opportunity_value] )

Without concrete sample data, it's hard for me to research further.

Best Regards,
Community Support Team _ Eason

[Anonymous]

@v-easonf-msft I have kept the calculated column to "dont summarize". for the requirements, measures are not allowed , they want to keep it in the rows section with a calculated column.

This new formula is also giving higher values.

[Shaurya]

Hi @Anonymous,

The problem is that you are creating a calculated column. GROUPBY and SUMMARIZE return a table so your formula will work fine when you use it to create a table, not calculated column.

Did I answer your question? Mark this post as a solution if I did!
Consider taking a look at my blog: How to Export Telemetry Data from Azure IoT Central into Power BI

[Anonymous]

@Shaurya hi. What is the way to get that requirement in a calculated column? 

[Shaurya]

Hi @Anonymous,

You can pick the required column from a DAX expression that returns a table and use the entire expression SELECTCOLUMNS(<DAX that returns Table>). Refer:

https://learn.microsoft.com/en-us/dax/selectcolumns-function-dax

Did I answer your question? Mark this post as a solution if I did!
Consider taking a look at my blog: How to Export Telemetry Data from Azure IoT Central into Power BI