Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Basket Type analysis

Hi All,

 

I have the following Sales Order Table:

 

Line_Item_IDSales_Order_IDSKU
114
211
324
431
537
634
744
851
958

 

What I want to do is calculate the average number of line items per sales order for each SKU. Then display the top N average for each SKU.

 

So in the above example, SKU 4 may be the most commonly bought, but SKU 1 has the highest number of line items. The end goal is to show our Sales team which products (when bought) are more likely to have multiple additional items purchased in a single Sales order.

 

For example - If you go to a store and buy a Lego model, you are likely to just buy a Lego model. Whereas the person that goes and buys an Airfix model will also buy Paint, Glue, Paint brushes, Sandpaper etc.

 

Hopefully someone can help with the Measure to calculate the average line items per invoice for each SKU.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    Here I have two ways to get the average.

    Calculated column:

    Avg = 
VAR _CountOrder =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Sales_Order_ID] ),
        FILTER ( 'Table', 'Table'[SKU] = EARLIER ( 'Table'[SKU] ) )
    )
VAR _OrderT =
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[SKU] = EARLIER ( 'Table'[SKU] ) ),
        'Table'[Sales_Order_ID]
    )
VAR _CountItem =
    CALCULATE (
        COUNT ( 'Table'[Line_Item_ID] ),
        FILTER ( 'Table', 'Table'[Sales_Order_ID] IN _OrderT )
    )
RETURN
    _CountItem / _CountOrder

    Result is as below.

    Measure:

    M_Avg = 
VAR _CountOrder =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Sales_Order_ID] ),
        FILTER ( ALL('Table'), 'Table'[SKU] = MAX('Table'[SKU] ) )
    )
VAR _OrderT =
    SUMMARIZE (
        FILTER (ALL( 'Table'), 'Table'[SKU] = MAX ( 'Table'[SKU] ) ),
        'Table'[Sales_Order_ID]
    )
VAR _CountItem =
    CALCULATE (
        COUNT ( 'Table'[Line_Item_ID] ),
        FILTER ( ALL('Table'), 'Table'[Sales_Order_ID] IN _OrderT )
    )
RETURN
    _CountItem / _CountOrder

    Result is as below.

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    If you want to get average number of line items per sales order for each SKU, you can build a calculated column as below.

    Average Per Sales_Order Per SKU = CALCULATE(AVERAGE('Table'[Line_Item_ID]),FILTER('Table','Table'[Sales_Order_ID] = EARLIER('Table'[Sales_Order_ID])&&'Table'[SKU]=EARLIER('Table'[SKU])))

    Then you can build a Rank column for TopN.

    Rank per Sku = RANKX(FILTER('Table','Table'[SKU] =EARLIER('Table'[SKU])),[Average Per Sales_Order Per SKU])

    Result is as below.

    In your sample, I am confused about the average of per Sales Order for each SKU is the same as Line Item ID.

    Your sample seems to be missing data under the same Sale_Order and the same SKU.

    If this reply still couldn't help you solve your problem, please share more details about your sample to me by your Onedrive for Business.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      The measure is clearly incorrect - the SKU "8" only appears in a single invoice, but that invoice has 2 line items:

      therefore the average number of line items for the SKU "8" is 2.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Your question is not clear.  How does SKU 1 have the highest number of line items?  Please clarify.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Sure - the SKU '1' appears in the following Orders:
      1, 3 and 5.

      For those Invoices, there are a total of 7 line items - 7 line items across 3 invoices = 2.3 average line items per invoice where that SKU is bought, where as the SKU of 4 also has 7 line items, but across 4 invoices, giving an average of 1.75 line items per invoice where that SKU is bought.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous 

        Here I have two ways to get the average.

        Calculated column:

        Avg = 
VAR _CountOrder =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Sales_Order_ID] ),
        FILTER ( 'Table', 'Table'[SKU] = EARLIER ( 'Table'[SKU] ) )
    )
VAR _OrderT =
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[SKU] = EARLIER ( 'Table'[SKU] ) ),
        'Table'[Sales_Order_ID]
    )
VAR _CountItem =
    CALCULATE (
        COUNT ( 'Table'[Line_Item_ID] ),
        FILTER ( 'Table', 'Table'[Sales_Order_ID] IN _OrderT )
    )
RETURN
    _CountItem / _CountOrder

        Result is as below.

        Measure:

        M_Avg = 
VAR _CountOrder =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Sales_Order_ID] ),
        FILTER ( ALL('Table'), 'Table'[SKU] = MAX('Table'[SKU] ) )
    )
VAR _OrderT =
    SUMMARIZE (
        FILTER (ALL( 'Table'), 'Table'[SKU] = MAX ( 'Table'[SKU] ) ),
        'Table'[Sales_Order_ID]
    )
VAR _CountItem =
    CALCULATE (
        COUNT ( 'Table'[Line_Item_ID] ),
        FILTER ( ALL('Table'), 'Table'[Sales_Order_ID] IN _OrderT )
    )
RETURN
    _CountItem / _CountOrder

        Result is as below.

        Best Regards,

        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.