Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kcantor
Community Champion
Community Champion

If has a value use A, else sum B -- DAX 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 Package numberDesired Result
121254HY6 $                     7.60 $                  6.35P16 $       6.35
161254HY3 $                     5.30 $                      -  P18 $       5.30
161254HY5 $                     4.60 $                      -  P22 $       4.60
181254HY2 $                     8.20 $                      -  P28 $       8.20
201254HY12 $                   12.00 $               19.65P29 $       6.55
201254HY7 $                     5.30 $               19.65P30 $       6.55
201254HY9 $                     4.60 $               19.65P41 $       6.55
251254HY1 $                     8.90 $                  6.35P15 $       6.35
281254HY10 $                     7.80 $                  5.60P52 $       5.60
291254HY8 $                     7.80 $                      -  P63 $       7.80




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@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)

FrieghtCalculation.png 

View solution in original post

2 REPLIES 2
Sean
Community Champion
Community Champion

@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)

FrieghtCalculation.png 

kcantor
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.