Forum Discussion

sanimesa's avatar
sanimesa
Post Prodigy
8 years ago
Solved

Need help with DAX for aggregation/conditional expressions

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.

 

 

Need Help
  • mattbrice's avatar
    mattbrice
    8 years ago

    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 )

     

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi sanimesa

     

    Try this

     

    Go to Modelling Tab>>>>NEW TABLE

     

    NEW Table =
ADDCOLUMNS (
    FILTER (
        SUMMARIZE (
            TableName,
            TableName[Customer],
            TableName[Order No.],
            "Total Invoice", SUM ( TableName[Invoice Total] )
        ),
        [Total Invoice] < 5000
            && CALCULATE ( SUM ( TableName[Shipping Charge] ) ) = 0
    ),
    "Shipping Cost", CALCULATE ( SUM ( TableName[Shipping Cost] ) )
)

12 Replies

  • mattbrice's avatar
    mattbrice
    Solution Sage
    8 years ago

    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's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    In the table that you have shared, please put in some values and more importantly, show your expected result.