cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrothfusz Frequent Visitor
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

 

PowerBI_Example.pngPowerBI_Example1.pngPowerBI_Error.png

 

 

Thnx,

Manfred

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
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])))


Capture1.PNG

If you have any question, please feel free to ask.

Best Regards,
Aneglia

5 REPLIES 5
tringuyenminh92 New Contributor
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

mrothfusz Frequent Visitor
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

PowerBI_workarround.png

tringuyenminh92 New Contributor
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).

v-huizhn-msft Super Contributor
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])))


Capture1.PNG

If you have any question, please feel free to ask.

Best Regards,
Aneglia

Highlighted
mrothfusz Frequent Visitor
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 Smiley Happy