5 Replies Latest reply on Apr 2, 2018 2:30 PM by Ramin Melikov

# Trying to answer prep question - In which Region do all Product Sub-Categories fall beneath the overall average profit?

This is the question:

###### e. West

The answer is D according to the guide.

I'm not even sure what it's asking. Is it asking which Regions do all avg profits of the Product Sub-Categories fall beneath the average profit for all regions?

I suspect this requires LOD expressions (or possibly window_avg?) which I've attempted to do but with no luck. I've been at this for over two hours and could use some help. Still trying to understand when to use LOD vs WINDOW_AVG expressions.

• ###### 1. Re: Trying to answer prep question - In which Region do all Product Sub-Categories fall beneath the overall average profit?

Slept on it and apparently that refreshed my brain. Woohoo, go brain!

Essentially, it's the sum of profit by sub-category. Then take the average of those sums of profits. The wording is awful in some of these questions, I hope it's not like this on the actual exam.

I was able to solve it in two ways. Creating a new calculation like so:

{ FIXED : avg({fixed [Product Sub-Category]  : sum([Profit]) })}

It also worked via two calculations using window_avg.

1. {fixed [Product Sub-Category]  : sum([Profit]) }

2. WINDOW_avg(min([fixed sum profit]))

77,202 is the average profit across product sub-categories (this can easily be verified if you dump the data into excel and create a pivot table). And we can easily see with a reference line that all product sub-categories in the South region are below this number.

I've attached the workbook.

• ###### 2. Re: Trying to answer prep question - In which Region do all Product Sub-Categories fall beneath the overall average profit?

Thanks for posting this solution.

{ FIXED : avg({fixed [Product Sub-Category]  : sum([Profit]) })} worked for me.

But I don't see why the outer FIXED does not have [Product Sub-Category]

• ###### 3. Re: Trying to answer prep question - In which Region do all Product Sub-Categories fall beneath the overall average profit?

Hi Gabriel,

I realize this is quite old but I was also struggling with this and came across this discussion. I don't believe Sub-Category is relevant at all; it just seems to be a happy coincidence that the results match

If you use this calculation:

WINDOW_AVG((SUM([Profit])))

- it matches the question they asked as well as providing the correct answer perfectly.

• ###### 4. Re: Trying to answer prep question - In which Region do all Product Sub-Categories fall beneath the overall average profit?

I think it depends on which year and which sample test questions you had.  The 10.4 prep exam question had product category, not sub-product category.  So I think everyone has the right solution, just depended on the sample question.

:-)

• ###### 5. Re: Trying to answer prep question - In which Region do all Product Sub-Categories fall beneath the overall average profit?

I think there is a better way of figuring this out. For the QA exam, one doesn't need to know a lot about LODs and Table Calcs and should still be able to answer this question. This problem can be solved in a few quick steps:

Step 1: Put SUM(Profit) on Columns shelf and Product Sub-Category on Rows.

Step 2: Go to Analytics Pane and drop an Average line on Entire Table

Step 3: Mouse over the average line and get its value (77202)

Step 4: Edit the Average line and change it to a Constant value of 77202

Step 5: Now drop Region onto the Rows shelf behind the Product Sub-Category.