If has a value use A, else sum B -- DAX Question

[kcantor]

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

[Sean · Accepted 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]

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.