Reply
Visitor
Posts: 1
Registered: ‎02-13-2017

Using a measure in the SUMMARIZE "Expression" field

Hi,

 

I'm trying to make a sum of a calculated column :

 

Trd_StdDev =

SUMX(

SUMMARIZE(

Database,

Database[CropYear],

"SumQuant",

(SUM(Database[Quantity]) - ([CropYear]*(Database[Trd_Slope])))

),

[SumQuant]

)

 

Idea here is to summarize the quantity by CropYear, grouping all other columns.

Then I want to create sum of all the rows but substracting a quantity which depends on : the CropYear and another measure called "Trd_Slope".

 

In my example, this measure is -286.56

 

When I replace Database[Trd_Slope] by its value -286.56, I get to the right calculation. But when I put back my measure "Database[Trd_Slope]", the text "Trd_Slope" becomes purple and the value of Trd_StdDev becomes "NaN"

 

Any idea on how I could use this measure in the "Expression" field of the SUMMARIZE function ?

 

Thanks in advance

Highlighted
Super Contributor
Posts: 4,721
Registered: ‎07-08-2016

Re: Using a measure in the SUMMARIZE "Expression" field

@blboun08

 

Please try to define a variable for your Trd_Slope measure in the expression like below.

 

Trd_StdDev =
VAR TrdS = ‘Measure Formula’
RETURN
    SUMX (
        SUMMARIZE (
            Database,
            Database[CropYear],
            "SumQuant", (
                SUM ( Database[Quantity] )
                    - ( [CropYear] * TrdS )
            )
        ),
        [SumQuant]
)

 

Best Regards,

Herbert