Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 )
Hi,
You may refer to my solution in this file.
Hope this helps.
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] ) ) )
Hi,
In the table that you have shared, please put in some values and more importantly, show your expected result.
Hi,
You may refer to my solution in this file.
Hope this helps.
@Ashish_Mathur It did not seem to work when there are multiple customers - not sure what I am doing wrong. I uploaded a pbix file with a new dataset with multiple customers. The second table is filtered down to the expected result.
Hi,
There was a mistake with your formula. Try this
=SUMX(SUMMARIZE('Shipment Data','Shipment Data'[Customer],'Shipment Data'[Order No.],"ABCD",if([Amount Invoiced $]<=5000,if('Shipment Data'[Total Shipping Charge]=0,SUM('Shipment Data'[Shipping Cost]),BLANK()),BLANK())),[ABCD])
@Ashish_Mathur I have similar problem but I have a date column for the transaction. I am using this date as a filter and hence I will need your help to do the same grouping takinging into consideration the date slicer. Can you please assist?
Thank you,
Hi,
Share some data and show the expected result.
You are welcome.
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] ) ) )
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |