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

Question

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 NumberTracking NumberShipping ChargeFreight Quote&nbsp;Package numberDesired Result121254HY6&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.60&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.35P16&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.35161254HY3&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.30&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;P18&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.30161254HY5&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.60&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;P22&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.60181254HY2&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.20&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;P28&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.20201254HY12&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 19.65P29&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.55201254HY7&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.30&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 19.65P30&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.55201254HY9&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.60&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 19.65P41&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.55251254HY1&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.90&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.35P15&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.35281254HY10&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.80&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.60P52&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.60291254HY8&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.80&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;P63&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.80

sean · Accepted Answer

kcantor&nbsp;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&nbsp;also by Order and Tracking Number (matrix)&nbsp;

Forum Discussion

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

2 Replies

Related content

Bookmark questions

Architecture question and brainstorming

Translate IF, ELSE IF, ELSE in DAX langage

Unpivot Question

Field Parameter Question

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