Average for some rows

[Dennis_Pedersen]

Hi 

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

[Anonymous]

[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.

[Dennis_Pedersen]

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 ?

[Anonymous · Accepted solution]

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

Best

Darek

[Dennis_Pedersen]

thanks a lot