Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |