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. 

     

     

v-xjiin-msft's avatar
v-xjiin-msft
Solution Sage
8 years ago

Hi chernni,

 

To achieve your requirement, you can refer to following method:

 

We can create calculated column with Earlier() function to get the prior row value as in your scenario, to get the prior date value. So the Frequency of order expression can be:

 

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

Then the expression of Frequent items can be:

 

Frequent items =
CALCULATE (
    COUNT ( 'Orders Fr'[Item.ID] ),
    ALLEXCEPT ( 'Orders Fr', 'Orders Fr'[Item.ID] )
)
    / DISTINCTCOUNT ( 'Orders Fr'[Date] )

The result shows like:

 

 

Thanks,
Xi Jin.

 

 

chernni's avatar
chernni
Frequent Visitor
8 years ago

v-xjiin-msft Thanks for the solution... but i`m struggling to understanding it.

 

I cannot use the function EARLIER properly, can you please share the power bi report that you`ve took the screen shot below? it might be easier for me to understand... tks!

    • chernni's avatar
      chernni
      Frequent Visitor
      8 years ago

      v-xjiin-msft thank you so much for you patience! but unfortunately, I cannot see the desired solution... 

       

      I can`t apply the `RANK ORDER` nor `FREQUENCY OF ORDER` measures... as images below...

       

       

      For the `FREQUENT ITEMS` formula, i used the following:

      Frequent items = CALCULATE(COUNT('db ORDERS'[ITEM.ID]),ALLEXCEPT('db ORDERS','db ORDERS'[ITEM.ID]))/DISTINCTCOUNT('db ORDERS'[ORDER.DATE])

       

      but the result is a bunch of `infinity` for each row of my table... as the image below...

       

      • v-xjiin-msft's avatar
        v-xjiin-msft
        Solution Sage
        8 years ago

        Hi chernni,

         

        => I can`t apply the `RANK ORDER` nor `FREQUENCY OF ORDER` measures... as images below... 

         

        You are using measure. Right? As I said before, I'm using calculated column not measure. Generally, earlier() function is used in calculated column. Same to Rankx. So to resolve your issue, you just need to change the measure to calculated column.

         

        Then for your Frequent items returns infinity. It seems like there exists 0 value in DISTINCTCOUNT('db ORDERS'[ORDER.DATE]). Did you apply any filter on your table?

         

        To troubleshoot your issue, you can separate the formula to two parts. See if there exists 0 values. And still use calculated column instead of measure. Also you can try Divide() function.

         

        Thanks,
        Xi Jin.

    • MAAbdullah_47's avatar
      MAAbdullah_47
      Helper V
      4 years ago

      Hi chernni  The pbix file is not available at the cloud drive you mentioned.