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 fa...
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,

     

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.

      • PowPow's avatar
        PowPow
        Frequent Visitor
        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,