topic Average for some rows in DAX Commands and Tips

Average for some rows

Dennis_Pedersen — Fri, 05 Jul 2019 12:30:33 GMT

Could you please help with a Dax Formula for calculating average production days?

In this case, I need the result 90 for vendor 7 and 70 for vendor 5. (2 orders each vendor). Number of lines/items is not relevant

The fact table:

Vendor number: Order number: Item Number: Production days:

7 100 50 80

7 100 51 80

7 188 50 100

5 20 50 60

5 20 51 60

5 25 50 80

Re: Average for some rows

Anonymous — Fri, 05 Jul 2019 13:10:09 GMT

[Avg Prod Days for Vendor] :=
var __vendor = VALUES( Orders[Vendor Number] )
var __oneVendorVisible = HASONEFILTER( Orders[Vendor Number] )
return
	if( __oneVendorVisible,
		AVERAGEX(
			SUMMARIZE(
				Orders,
				Orders[Order Number],
				Orders[Production days]
			),
			Orders[Production days]
		)
	)
	
-- The assumption is that
-- each order number has the same production days
-- for each of its item numbers. If this is not
-- true the code will return wrong results. But this
-- assumption seems reasonable based on the sample
-- data supplied.

Re: Average for some rows

Dennis_Pedersen — Mon, 08 Jul 2019 06:48:53 GMT

Hi Darlove

Thanks a lot. That works just fine.

That gives the correct figure for each vendor. Is there any way to calculate the correct average for alle vendors ?

Re: Average for some rows

Anonymous — Mon, 08 Jul 2019 09:05:44 GMT

[Avg Prod Days] :=
AVERAGEX(
	VALUES( Orders[Vendor Number] ),
	[Avg Prod Days for Vendor]
)
-- This works OK even if one vender is visible.

Best

Darek

Re: Average for some rows

Dennis_Pedersen — Mon, 08 Jul 2019 10:28:53 GMT

thanks a lot