How to calculate the average delivery days

DAX DAX Calculations
1

Hi, I have a transaction table and each order has a field called delivery days. The challenge is the my transaction table has multiple SKU number for each order, which means when I sum, the total delivery days will be added several times. How can I use DAX to calculate the average correctly?

Thanks!

Mike

Average_Dilivery_Days.pbix (35.4 KB)

2

@Mike What I understood is that the Average in the total is not computed correctly. If that’s the case then you need an iterator to get the correct average days

Avg Days = 
AVERAGEX('Table','Table'[Delivery Days])

Average_Dilivery_Days.pbix (34.3 KB)

Thanks.

3

@MudassirAli Thanks, I am just wondering why the average it 1.4 instead of 1.5? Because there are two orders with delivery day 1 and 2, I was assuming the result should be 1.5. Thanks!

4

Have you considered taking your order data and removing the item specific fields, removing duplicates and then calculating from a much smaller table so things dont iterate through unnecessary rows.

5

Hi @Despo, that was one of my option. I could have one older table with all delivery time data and link to Fact_sales table, however, due to product table linked to Fact_sales table as well, I think I will have problem with filter direction on the delivery date measure. I will try to see if it is working. Thanks for your advice.

Thanks!

Mike

6

@Mike I tried to solve it in power query and now for your OrderID, the average will be shown accurately.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQr2DgVRhkqxOnABI3QBY7iAEUTAFEgZIQuYQQRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderID = _t, SKU = _t, #"Deliver Days" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderID", Int64.Type}, {"SKU", type text}, {"Deliver Days", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Deliver Days", "Delivery Days"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "OrderID", "SKU", "Delivery Days"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each if [Index]=1 then null else #"Reordered Columns"{[Index]-2}[Delivery Days]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Final Delivery Days", each if [Custom] = null then [Delivery Days] else if [Custom] <> [Delivery Days] then [Delivery Days] else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Final Delivery Days", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom"})
in
    #"Removed Columns"

Attaching the PBIX file. Let me know if it works.

Average_Dilivery_Days.pbix (33.8 KB)

Thanks.

7

Hi @Mike, did the response provided by @MudassirAli and @Despo help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

8

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!