Return calculated value from pivot for set dimension

[#1]

I am trying to return the results of a calculated value while looking at one set dimension for a pivot. For example I have a pivot looking at the Grade and Gender of a person and want to return the results of only a Gender of M regardless of what the actual Gender is of the line. I have an example of my data below, the expected result always returns the avg rate of the Grade for the M Gender.

Grade	Gender	Avg Rate	Expected Result
1	F	60	50
	M	50	50
2	M	70	70
3	F	80	90
	M	90	90

I have tried many different expressions but cannot get any to consistently return the right value.

Thanks,
Matt

[rubenmarin]

Hi Matt,

The expression for expected result should be something like:

Avg({<Gender={'M'}>} Rate)

[#3]

That is only adding the average to the row with the M Gender and a 0 for all rows with a F Gender.

[JonnyPoole]

maybe this. but i'd feel better if you post the raw data or the qvw

if(

     Gender = 'M',    sum(  [Avg Rate] ) ,

     if ( Gender = 'F', sum ( {$<Gender={'M'}>} [Avg Rate] ) )

)

[#5]

Jonathan,

Thanks for your help but that did not work either. Sorry, I am new and do not know how to upload my data.

[JonnyPoole]

See if this will work:

if ( Gender='F',  sum(  total <Grade> [Avg Rate]) - sum([Avg Rate]) , sum([Avg Rate]))

I tried it with the few rows of data you had in your first post. 

Otherwise, when you reply, go to 'Use Advanced Editor' and then you can attach QVW files or data files.

[#7 · Accepted solution]

Jonathan,

That worked with a slight modification. Below is what I used:

if ( Gender='F',  (sum(  total <Grade> [Avg Rt]) - sum([Avg Rt]))/(count(total<Grade> [Unique ID]) - count( total <Grade,Gender> [Unique ID])) , avg([Avg Rt]))

Unique ID was just another field I had but probably could have used any field in place for that count.

Your formula worked if there were an equal number of people by gender. I was able to get the counts of each gender so that the sum total was divided by the number of 'M' to get the average for the 'M'. This may have been taking a longer way than needed to solve the problem but it worked.

Thank you very much for your help! Greatly Appreciated!

[JonnyPoole]

Great. Kindly close out the thread with the correct/helpful messages.