Hi!
I need some help!!!

I have FactTable:

ID	Oder ID	Product ID	Order Totul Sum	Date ID
33	4	123	3	1
34	4	43	3	1
35	5	23	4	2
36	5	12	4	2
37	5	2	4	2
38	6	1	6	2

I need to get this:
AVG Order Total Sum	Date ID
3	1
5	2

Or that:
SUM Distinct Order Total Sum	Date ID
3	1
10	2

Any help on this would be greatly appreciated.

• Edited by z97l Friday, January 30, 2015 5:56 PM

Is it possible to break the OrderTotalSum column down by line? That would simplify the calculation and give you more capabilities.

If that's not possible, then you could fake it by adding a calculated column (e.g. SalesLineTotal_EqualParts) where you simply divide the OrderTotal by the number of lines on the order...

Then you could create the following measures...

1. [OrderCount] - using DistinctCount aggregation on OrderID column
2. [Sales Amount Eq Weight] - using Sum aggregation on the SalesLineTotal_EqWeight

Now you're [AVG Order Total Sum] and [SUM Distinct Order Total Sum] are much easier to write...

WITH
	MEMBER [Measures].[AVG Order Total Sum] AS
		IIF(
			 IsEmpty([Measures].[OrderCount])
			,Null
			,([Measures].[Sales Amount Eq Weight]/[Measures].[OrderCount])
		)
		,FORMAT_STRING = "Currency"
	MEMBER [Measures].[SUM Distinct Order Total Sum] AS
		[Measures].[Sales Amount Eq Weight]
		,FORMAT_STRING = "Currency"

SELECT	
	{ 
	  [Measures].[AVG Order Total Sum] 
	 ,[Measures].[SUM Distinct Order Total Sum]
	} ON 0,
	{ [Date].[Date].[Date] } ON 1
FROM
	[SalesCube]
;

... you also would probably want to consider moving the calculations for  [AVG Order Total Sum] and [SUM Distinct Order Total Sum] into the MDXScript of the cube (for better cache utilization) and at the same time it would probably be a good idea to set the visible property of the [Sales Amount Eq Weight] to false (since it could be misleading if used in order line level calculati