- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: EARLIER against a measure

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

mrothfusz

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2016
05:08 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-huizhn-msft

Super Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-11-2016
11:47 PM

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

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

Best Regards,

Aneglia

5 REPLIES 5

tringuyenminh92

New Contributor

Re: EARLIER against a measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2016
06:15 AM

Hi @mrothfusz,

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

mrothfusz

Frequent Visitor

Re: EARLIER against a measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2016
07:35 AM

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

tringuyenminh92

New Contributor

Re: EARLIER against a measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-11-2016
08:01 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-11-2016
11:47 PM

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

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

Best Regards,

Aneglia

Highlighted
##

mrothfusz

Frequent Visitor

Re: EARLIER against a measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-12-2016
12:09 AM

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