topic Re: Help to find DAX to Average Vlookup Value Across Multiple Rows With Same Unique Identifier in DAX Commands and Tips

Help to find DAX to Average Vlookup Value Across Multiple Rows With Same Unique Identifier

Anonymous — Mon, 12 Jul 2021 20:14:37 GMT

Hello,

So, I'm trying to figure out a DAX that will take the Lookup (Vlookup) value and average across multiple rows (part numbers), based on the count of rows with a unique identifier (Document #). I'm thinking it should be the LOOKUP value divided by the COUNTIF function, but not sure...can someone provide some insight on how to properly do this?

In the below screen shot example you can see, Document #7374 has 3 different "WOMP Tag #" these are part numbers created on each Document. However, on the far right Vlookup Costs & Vlookup Hrs are allocating the totals for that Document 3 different times. I need the totals averaged by 3 or by sum of rows counted with same Document #.

Regards,

Kyle

Re: Help to find DAX to Average Vlookup Value Across Multiple Rows With Same Unique Identifier

dedelman_clng — Thu, 29 Jul 2021 20:30:05 GMT

Hi Anonymous -

Based on what you described, I think you can use something like AVERAGEX

VLookup_Avg = AVERAGEX ( VALUES ( '2021 Production'[Document #] ), '2021 Production'[Vlookup No] )

Without having your data I wasn't able to test, so see if this gets you were you want to go.

Hope this helps

David

Re: Help to find DAX to Average Vlookup Value Across Multiple Rows With Same Unique Identifier

Anonymous — Fri, 30 Jul 2021 12:42:25 GMT

Thanks for the input. I actually ended up creating another table and through Power Query Editor replicated a CountIF function to count number of lines for each Document #, then through a lookup formula divided that by total costs & labor hours. Probably not the most efficient way, but everything works now! Examples below...

Count Doc# Lines = LOOKUPVALUE ( 'W.O. Qty Table '[Count Part #] , 'W.O. Qty Table '[Document #] , '2021 production'[Document #] , 'W.O. Qty Table '[Document #] , '2021 production'[Document #] )

Avg Costs = DIVIDE ( '2021 production'[Vlookup Costs] , '2021 production'[Count Doc# Lines] )

Avg Hrs = DIVIDE ( '2021 production'[Vlookup Hrs] , '2021 production'[Count Doc# Lines] )