Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

Average for some rows

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

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

Best

Darek

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

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
Not applicable

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

Best

Darek

thanks a lot

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors