Average Order Lines Count

[PowPow]

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.

[HarrisMalik]

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]

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]

@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 · Accepted solution]

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,

[leonardmurphy]

Storing order number and order line in the fact table is a legitimate star schema design (called a degenerate dimension):

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimens...

A dimension that has the same number of rows as a fact table is a heads up that a degenerate dimension might be useful. 

(Sorry if you already know this)

[PowPow]

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