Do we need both a Fact_Order and a Fact_Orderlines table in the model?

[kk_shp_user]

This is a data modelling question. I have shopify fact_orderline table which is basically a line level fact. I am wondering if I should also have an order level table in my data model.

The question arises because, I need to compute the avg. and median days it takes for someone to make a repeat order. My Fact_Orderline table has a 'Days_Since_Previous_Order' field. But the challenge is, this data exists in all the lines of a single order.

So, if I do a simple average of that column, I would not get the proper average days it takes to place repeat orders.

1. Is there any way, I can compute the average days to reapeat from the Fact_Orderlines table itself? I tried a combination of AVERAGEX + CALCULTE but I could not get the right answer

2. Is it always better to have order-level stats like this measure, items in order, totl order value, etc in an order level fact table?

[mark_endicott]

@kk_shp_user - You can use AVERAGEX for this:

AVERAGEX( VALUES( Table[ORDERID] ), MAX( Table[DAYS_SINCE_PREV_ORDER] ))

Please accept as the solution if this works for you, it helps others with the same challenge. 

[kk_shp_user]

Thanks! I'll try this. Is there a way to add a filter condition on the table while doing this.

I want to remove the first orders made by customers as they are not repeat orders and will not have the DAYS_SONCE_PREVIOUS_ORDER figure

[mark_endicott]

@kk_shp_user 

IF (
    NOT ( ISBLANK ( Table[DAYS_SINCE_PREV_ORDER] ) ),
    AVERAGEX ( VALUES ( Table[ORDERID] ), MAX ( Table[DAYS_SINCE_PREV_ORDER] ) )
)

Or just use the ORDER_SEQ_NO as a filter on the visual through the Filter pane, untick 1. 

[kk_shp_user]

I'm not able to use columns in NOT(ISBLANK()) mentioned above. It only takes measures as inputs.

I also tried the first measure you mentioned. It gives the maximim values for 'DAYS_SINCE_PREVIOUS_ORDER' inb the table. It does not change even if I filter that column. I filtered it to only consider values less than 50 and it stil shows avg as greater than 2000

[mark_endicott · Accepted solution]

@kk_shp_user  - Ok, I've fixed up all the issues in this now. Use the DAX below

VAR _table =
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[ORDERID],
            "@avg", AVERAGE ( 'Table'[DAYS_SINCE_PRV_ORDER] )
        ),
        NOT ISBLANK ( 'Table'[DAYS_SINCE_PRV_ORDER] )
    )
VAR _avg =
    AVERAGEX ( _table, [@avg] )
RETURN
    _avg

I've attached a file to show this works, the code above is in Measure. If you look at Measure2 I've taken away the filter, in the DAX but added my suggested filter to the table from the column. 

[kk_shp_user]

This worked!! Thanks!

[kk_shp_user]

Thanks so much! I will confirm this by end of the week!