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,

     

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,

     

      • PowPow's avatar
        PowPow
        Frequent Visitor
        10 years ago

        Thanks for your input leonardmurphy

         

        I am familiar with the concept of degenerate dimensions but in this particular case i had two concerns. First is that my transaction dimension actually includes several other relevant fields and hierarchies and so i can't just get rid of the dimension.

        The other part is related to keeping the pover pivot model as "lean" as possible. Since data is stored by column duplicating such high cardinality columns is expensive.

         

        For the moment i am testing on only a few million rows and haven't really noticed any performance issues but later on the full data set i might need to revisit the approach or try optimizing the model. Plenty of awsome resources about it here if interested.

         

        Regards