Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
[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 ?
[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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |