• Tuesday, April 17, 2012 6:24 PM

Suppose a company has two sales types and two branches. I want a calculated measure that gives the average sales for the company if the company were to have the same sales type count distribution as the branch (for comparison purposes). For example, Branch A's average sales are \$15, and the companywide comparison for Branch A (what I want to calculate but don't know how) would be SUM(Type Average*Branch Type Sales Count)/(Branch Sales Count)={[(15+20)/(2+3)]*2 + [(30+25)/(1+4)]*1} / (2+1) = (14 + 11)/3 = 8.33.

NOTE: The table below is not representative of my fact table, it is only showing the data for the above calculation.

Type/Branch/Sales/Sales Count

1 A 15 2

1 B 20 3

2 A 30  1

B 25 4

I think the calculation should look something like:

AVG(

([Sales Type].[Type],[Branch].[Branch Name]),

(Expression that gives the companywide average sales for type) * [Measures].[Sales Count]

/ (Expression that gives sales count for branch for all types)

)

Essentially, I think I need to do an AVG with part of the "numeric expression" at a higher level than the "set expression."

Thanks!

• Edited by Tuesday, April 17, 2012 6:25 PM
• Edited by Tuesday, April 17, 2012 6:25 PM
• Edited by Tuesday, April 17, 2012 8:04 PM
•