cancel
Showing results for
Did you mean:
Frequent Visitor

## EARLIER against a measure

Hi,

I’ve got a measure (named Output) which is a SUMX formula with some calculations against the Number columns.

Now I want to have an AVG (and later a MIN and MAX) of some values from the Output and the criteria is the ID1 column.

Example: I’ve got two values in ID1 with 6 so the AVG of the two output values is 1360.79 and I’ve got four values with 14 so the AVG of the four output values is 1443.68

Normally I’ll go for the EARLIER formula, but that won’t work against a measure (error because Output isn’t a column but a measure)

Any ideas? I’ve put some printscreens in the post

Thnx,

Manfred

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: EARLIER against a measure

Hi @mrothfusz,

You’d better create a calculated column rather than measure. You can get the same result without SUMX function. I try reproduce your scenario and get the expected result.

First, try to create a calculated column using the following formula. Then calculate the average based on new calculated column.

`Output = Table1[Number1]*0.75+Table1[Number2]*0.2+Table1[Number3]*0.125+Table1[Number4]*0.2+Table1[Number5]*0.75`
`Ave = CALCULATE(AVERAGE(Table1[Output]),FILTER(Table1,Table1[ID1]=EARLIER(Table1[ID1])))`

Best Regards,
Aneglia

5 REPLIES 5
New Contributor

## Re: EARLIER against a measure

Hi @mrothfusz,

Earlier and earliest methods are used in Calculated Column. Please check again and understand that context

Highlighted
Frequent Visitor

## Re: EARLIER against a measure

Hi,

Yes I know that. I want to create a calculated column but I need data from a measure in combination with EARLIER. I can't change the measure to a calculated column because I need to use SUMX. I need to use SUMX instead of SUM because I want to do some calculations against values in some columns.

I can't find the solution to mix them (I'am a newbee)

EDIT:

Found a workarround by first calculate the AVG of some columns (create calculated colums) and then doing the SUMX (measure). I've create a printscreen of it. Maybe someone got a better idea

New Contributor

## Re: EARLIER against a measure

Yeah @mrothfusz. Haha, I have same workaround solution like you when doing fluktuation (%) in 12 months and YTD fluktuation (= total of percentage of 12 months).

Super Contributor

## Re: EARLIER against a measure

Hi @mrothfusz,

You’d better create a calculated column rather than measure. You can get the same result without SUMX function. I try reproduce your scenario and get the expected result.

First, try to create a calculated column using the following formula. Then calculate the average based on new calculated column.

`Output = Table1[Number1]*0.75+Table1[Number2]*0.2+Table1[Number3]*0.125+Table1[Number4]*0.2+Table1[Number5]*0.75`
`Ave = CALCULATE(AVERAGE(Table1[Output]),FILTER(Table1,Table1[ID1]=EARLIER(Table1[ID1])))`

Best Regards,
Aneglia

Frequent Visitor

## Re: EARLIER against a measure

Yesterday I've created this one (instead of the SUMX measure)

`Output = ([Number1] * 0.75) + ([Number2] * 0.2) + ([Number3] * 0.125) + ([Number4] * 0.2) + ([Number5] * 0.75)`

Then I can do the

```AVG = CALCULATE (
AVERAGE(RAW[Output]),
FILTER(RAW,RAW[ID1]=EARLIER(RAW[ID1])))```

Works so far :-)

EDIT:

I see that we almost do the same, only you use (like it should be) the table name in the calculated column. The other code I already received from you