Forum Discussion

chernni's avatar
chernni
Frequent Visitor
8 years ago
Solved

Calculating Orders Frequency

Hi,   i`m quite new to PowerBi and I need some help...   I have a database with orders, with info of CUSTOMER, ORDER.DATE, ORDERS.QTY., and ITEM.ID. What I`m trying to achieve here is: figure it ...
Need Help
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    Hi chernni,

     

    For the two questions, they all based on how to make groups.

     

    1. Frequency of order. Add 'Orders Fre'[Customer] = EARLIER ( 'Orders Fre'[Customer] ) inside the Filter to make the formula be based on Customer group. Then it will get the next date within the same customer group not the next physical row.

     

     

    Frequency of order =
DATEDIFF (
    CALCULATE (
        MAX ( 'Orders Fre'[Date] ),
        FILTER (
            'Orders Fre',
            'Orders Fre'[Date] < EARLIER ( 'Orders Fre'[Date] )
                && 'Orders Fre'[Customer] = EARLIER ( 'Orders Fre'[Customer] )
        )
    ),
    'Orders Fre'[Date],
    DAY
)

     

     

    2. Frequent items. Same issue. In my prior expression I'm using ALLEXCEPT ( 'Orders Fr', 'Orders Fr'[Item.ID] ) to make the formula be based on only Item.ID group. That's why all the same Item.ID got the same percentage. So to resolve your issue, add one more condition in ALLEXCEPT().

     

    Frequent items =
DIVIDE (
    CALCULATE (
        COUNT ( 'Orders Fre'[Item.ID] ),
        ALLEXCEPT ( 'Orders Fre', 'Orders Fre'[Item.ID], 'Orders Fre'[Customer] )
    ),
    DISTINCTCOUNT ( 'Orders Frequency'[Date] )
)

     

     

    Little tips: the most important point in your requirement is to make groups for your data. And generally in DAX, we can use EARLIER() or ALLEXCEPT() function to ahieve this. EARLIER() is used in calculated column and ALLEXCEPT() can be use in both measure and calculated column. 

     

    I think I have shown you the right direction. Please make more effort and try to tune the formula on yourself. :smileyhappy:

     

    Thanks,
    Xi Jin. 

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Share a sample dataset and for that sample show the expected result.

chernni's avatar
chernni
Frequent Visitor
8 years ago

Hi Ashish_Mathur, thanks for your response. Check below the request:

 

In the image below we have a simple example with a single customer "JDS", with two orders.

 

 

Order 1: Jan 11st, including two products (ITEM.ID), one unit each.

Order 2: Feb 23rd, including three products (ITEM.ID), one unit each.

 

As a result I'd like something like:

 

Frequency of order: (Feb 23rd) - (Jan 11st) = 43 days.

Frequent items: 3543 (100% of orders), 3898 (100% of orders) and 3912 (50%) of orders.

 

Something like that.

 

Another example, with three orders:

 

As a result here, I'd have as a result:

 

Frequency of order: (38+82)/2= 60 days (details on image below)

 

Frequent items: 1201 (100% of orders).

 

Hope i made myself clear... If not, please let me know! thanks for the support!!! 

 

Btw, if needed I can give more complex examples, with more orders, or multiple items, but my goal is to identify the average frequency of order and average items requested by a given customer.

 

Thanks!!!