Need help with DAX for aggregation/conditional expressions

[sanimesa]

I am doing analysis of freight charges and the data looks something like this:

Customer        Order No.           Invoice No.     Invoice Total       Shipping Charge     Shipping Cost

-------------------------------------------------------------------------------------------------------------

ABC                  111                  1111               1000.00                             0.00                 30.00

ABC                  111                  1112               2000.00                             0.00                 30.00

ABC                  111                  1113               1000.00                             0.00                 20.00

ABC                  111                  1114                 500.00                             0.00                 10.00

ABC                  222                  2221               2000.00                             0.00                 10.00

ABC                  222                  2222               2000.00                             0.00                 15.00

ABC                  222                  2223               2000.00                             0.00                 25.00

ABC                  333                  3331                 500.00                           10.00                   7.00

ABC                  333                  3332                 250.00                             5.00                   4.00

ABC                  333                  3333                 250.00                             5.00                   4.00

Ergo, for each customer there are multiple orders, orders might be split into multiple shipments. 

Assume that there is a shipping thresold of $5,000, i.e. for orders above that amount shipping is free. However, there are exceptions. Also, there is not always a shipping cost.

I need to find out for each customer, for orders with total order value less than 5,000 and if there is a shipping cost, how many orders were not charged a shipping fee and what is the total loss due to this. 

Need some directional help with this - I understand that first I need to aggregate the values for each order and then run an analysis on the aggregates. Not sure how to approach it in DAX. 

Update: In the above example, Order 1111 has total value of 4,500 but no shipping cost is being charged. Order 222 is 6,000 hence eligible for free shipping. Order 333 is being charged as expected. So, total loss is 90 and the number of orders matching the exception condition is 1.

[mattbrice · Accepted solution]

If you put a Matrix on the page and only put Customers on the rows, these measures should work:

Amount Shipping Cost for Orders less than 5k and have shipping cost = 
VAR Orders_less_5_with_ship_Cost =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table1[Order No.] ),
            "Total_Amount", CALCULATE ( SUM ( Table1[Invoice Total] ) ),
            "Total_Ship_Cost", CALCULATE ( SUM ( Table1[Shipping Cost] ) ),
	     "Total_Ship_Charge", CALCULATE( SUM( Table1[Shipping Charge] ) )
        ),
        [Total_Amount] < 5000
            && [Total_Ship_Cost]  && NOT( [Total_Ship_Charge] )
    )
RETURN
    CALCULATE( SUM( Table1[Shipping Cost] ), Orders_less_5_with_ship_Cost )

and

Count Orders less than 5k and have shipping costs = 
VAR Orders_less_5_with_ship_Cost =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table1[Order No.] ),
            "Total_Amount", CALCULATE ( SUM ( Table1[Invoice Total] ) ),
            "Total_Ship_Cost", CALCULATE ( SUM ( Table1[Shipping Cost] ) ),
			"Total_Ship_Charge", CALCULATE( SUM( Table1[Shipping Charge] ) )
        ),
        [Total_Amount] < 5000
            && [Total_Ship_Cost] && NOT( [Total_Ship_Charge] )
    )
RETURN
    COUNTROWS( Orders_less_5_with_ship_Cost )

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in this file.

Hope this helps.