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. 

     

     

chernni's avatar
chernni
Frequent Visitor
8 years ago

v-xjiin-msft awesome! it worked! But it might not be exactly what i need... What`s showing me in the image below is the following: `Frequency of order` shows me the difference, in days, from the last order indepently of the customer which can be a valuable information as well. But what i`m actually looking for is, the difference, in days, between orders from the same customer.

 

Let`s take as an example customer `FABRICA`, the line from Jan 15th. What i`d like to see in the `Frequency of order` would be jan 15 - Jan 9 = 6 days (instead of Jan 15 - Jan 11 = 4 days). Is it possible to do something like that?

 

Also, it appears the percentages in the `Frequent items` columns are global... related to the whole database... take as an example ITEM.ID = 3898, in every line is 87.32%, as per my understanding it means in 87.32% of the orders this item goes into. Which is a nice info, but i`m also looking for the info by customer... For customer A it can be 50%, customer B 100%, C 90% and from that on...

 

Can you please help ou with that last detail? Thank you so much !!

 

v-xjiin-msft's avatar
v-xjiin-msft
Solution Sage
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.