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.
I am working on a calculation within a single table for shipping charges. For each order number there is a tracking number field, and a couple of associated cost fields: Shipping Charge and Freight Quote. I need to create a calcuation (column or measure) that will return a value based upon the contents of those cells. The calculations would be (in plain English) If a freight quote exists, divide the freight quote by the distinct number of tracking numbers and apply that cost across all tracking numbers, if a freight quote does not exist, use the shipping charge that exists per package. I want to display the result by tracking number, not by order number.
Any help will be greatly appreciated.
Sample data:
Order Number | Tracking Number | Shipping Charge | Freight Quote | Package number | Desired Result |
12 | 1254HY6 | $ 7.60 | $ 6.35 | P16 | $ 6.35 |
16 | 1254HY3 | $ 5.30 | $ - | P18 | $ 5.30 |
16 | 1254HY5 | $ 4.60 | $ - | P22 | $ 4.60 |
18 | 1254HY2 | $ 8.20 | $ - | P28 | $ 8.20 |
20 | 1254HY12 | $ 12.00 | $ 19.65 | P29 | $ 6.55 |
20 | 1254HY7 | $ 5.30 | $ 19.65 | P30 | $ 6.55 |
20 | 1254HY9 | $ 4.60 | $ 19.65 | P41 | $ 6.55 |
25 | 1254HY1 | $ 8.90 | $ 6.35 | P15 | $ 6.35 |
28 | 1254HY10 | $ 7.80 | $ 5.60 | P52 | $ 5.60 |
29 | 1254HY8 | $ 7.80 | $ - | P63 | $ 7.80 |
Proud to be a Super User!
Solved! Go to Solution.
@kcantor Try this...
Calculated Column = IF ( ISBLANK ( 'Table'[Freight Quote] ), 'Table'[Shipping Charge], DIVIDE ( 'Table'[Freight Quote], CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Order Number] ) ), 0 ) )
Seems to work the way you want to show by Tracking Number (table) and also by Order and Tracking Number (matrix)
@kcantor Try this...
Calculated Column = IF ( ISBLANK ( 'Table'[Freight Quote] ), 'Table'[Shipping Charge], DIVIDE ( 'Table'[Freight Quote], CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Order Number] ) ), 0 ) )
Seems to work the way you want to show by Tracking Number (table) and also by Order and Tracking Number (matrix)
Beautiful. Thank you for helping me out on that. I did have to alter the formula to account for zero instead of blank but it worked perfectly.
Proud to be a Super User!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |