Dimesional Metric Issue (not grouping in SQL)

[cheesyman5k]

I have two Attributes Parent and Child (Parent has many Children)
I have a metric called COST which is defined as max(Cost) at the Child level
I have another metric called visits which is a count of records the a Child has in the visits table.
Lastly I have a compound metric called REV which is (COST*Visits)/1000

My problem is, if I have a report with Parent only, the MAX(Cost) of all of the Children is used in the calculation, not what I need to happen.

I need for MSTR to first calculate the REV at the Child Level, and then sum up to Parent.

I thought if I made Cost and Visits dimensional metrics at the Child Level, then I would get the results that I was looking for, but it’s not working.

Here is the raw data sample
parent child visits cost
1 A 2 1000
1 B 2 100

Here is what a report looks like with Child in the results (this is correct!)

Parent Child Visits REV
1 A 2 2.00
1 B 2 .20

When I remove Child from the report this is what I am getting (this is Incorrect!)

Parent Visits REV
1 4 4.00


Here is what I need for the report result.

Parent Visits REV
1 4 2.20

Sorry. The sample data isn't lined up to well.

I know if we had OLAP Services, I could simply add Child to the report but not display it in the report, but we don't have OLAP services.

So, am I missing something here?

Thanks in advance,

 

[reisw]

Have you tried creating a base formula and then using that in your calculation? Make the base formula Visits * Cost and then use that in your calculation (Base Formula/1000).

That should work I think.