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
sanimesa
Post Prodigy
Post Prodigy

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.

 

 

3 ACCEPTED SOLUTIONS
mattbrice
Solution Sage
Solution Sage

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 )

 

View solution in original post

Hi,

 

You may refer to my solution in this file.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

Regards
Zubair

Please try my custom visuals

View solution in original post

12 REPLIES 12
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  I added some values and also added the expected result.

Hi,

 

You may refer to my solution in this file.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

File: https://1drv.ms/u/s!AqfAbaFIezosaaTM4wN1B4LEmRg

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur My bad, there was a typo. Now it works great, thanks!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Regards
Zubair

Please try my custom visuals

mattbrice
Solution Sage
Solution Sage

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 )

 

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.