Forum Discussion

PowPow's avatar
PowPow
Frequent Visitor
10 years ago
Solved

Average Order Lines Count

Hi all,

 

having trouble wrapping my head around a measure calculation and would appreciate some pointers.

 

I have power pivot model based on a data warehouse (perfect star schema). The sales fact table is linked to the sales order dimensions via a surrogate key. The fact grain is the order line. The order dimension includes the order number and the line number.

 

I am trying to create a measure that would give me average order lines in the chosen pivot context (date, item, customer...). I have managed to get this working but only when i use the order dimension in the pivot. I have created two measures:

 

Lines = AVERAGEX(VALUES('OrderDIM'[OrderNo]); DISTINCTCOUNT('OrderDIM'[OrderLineNo]))

AverageLine = AVERAGEX(DISTINCT('OrderDIM'[OrderNo]); [Lines])

 

I suspect my problem is that my two calculations are based of the dim table and i should somehow include the fact but i am little out of my league with DAX. Or maybe i should just bring the Order Number and Order Line to the fact table and then build a measure from there? Any help would be appreciated.

Need Help
  • PowPow's avatar
    PowPow
    10 years ago

    Hi Harris,

     

    I've managed to solve it by bringing Order Number and Order Line to the fact table and then creating a measure like so:

     

    =AVERAGEX(SUMMARIZE('fact';'fact'[OrderNo];"AVG";DISTINCTCOUNT('fact'[OrderLineNo]));[AVG])

     

    Not sure this is the best approach but it works.

     

    Thanks for your help,

     

6 Replies

  • HarrisMalik's avatar
    HarrisMalik
    Continued Contributor
    10 years ago

    Hi

     

    I think you are making it complex. What is the reason for not using simple DistinctCounts for orders and orderlines and then divide them, something like:

     

    AverageOrderlines = DIVIDE(DISTINCTCOUNT(Table[OrderlineNo]),DISTINCTCOUNT(Table[OrderNo]))

     

    Cheers

    Harris

    • PowPow's avatar
      PowPow
      Frequent Visitor
      10 years ago

      Thanks for your answer Harris,

       

      Your suggestion only gives me correct results when looking specifically at the order number level in the pivot. Any other aggregation level within the order dimension is wrong.

      And using any other dimension in the pivot is also wrong as well. 

       

      Regards,

      • HarrisMalik's avatar
        HarrisMalik
        Continued Contributor
        10 years ago

        PowPow Can you elaborate it with some screen shots, what you see now and what is correct?

         

        Secondly the discussion here is in the context of Power BI.