Forum Discussion
Average Order Lines Count
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,
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
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,
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.
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,
Storing order number and order line in the fact table is a legitimate star schema design (called a degenerate dimension):
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)
