
1. Re: How to calculate average based on max denominator
Mahfooj Khan Jun 6, 2016 7:22 PM (in response to Emily Z)Is that what you wanted?
Workbook (public version 9.3) has been saved in my public profile. Have a look.
Feel free to ask If you've any question.
Mahfooj

2. Re: How to calculate average based on max denominator
Emily Z Jun 6, 2016 8:08 PM (in response to Mahfooj Khan)Many thanks Mahfooj!
Your solution only works when there's only one category. Would you mind use the excel I attached which has more than one category?

3. Re: How to calculate average based on max denominator
Mahfooj Khan Jun 6, 2016 9:29 PM (in response to Emily Z)Okay! I've done some formatting in your data. Used pivot in tableau.
just have a look I've attached a new workbook (version 9.2)
Feel free to ask If you've any question.
Mahfooj

Avg and % Total.twbx 18.7 KB


4. Re: How to calculate average based on max denominator
richard.sonkane Sep 20, 2016 7:07 AM (in response to Emily Z)To simplify the fractions and it's formula it's need that the multiply the numerator an dthen the denominator the both them values has been same.in case of the vlaue of formula then multiple level top and bottom s1*s2
2 * D / (D/S1 + D/S2)
Multiply top and bottom by S1 * S2
= 2 * D * S1 * S2 / ((D/S1 + D/S2) * S1 * S2)
Distribute the multipliers in the denominator
= 2 * D * S1 * S2 / (D * S2 + D * S1)
Factor out the D from the denominator (distributive law)
= 2 * D * S1 * S2 / D (S2 + S1)
Cancel the D factor from top and bottom
= 2 * S1 * S2 / (S2 + S1)
Addition is of course commutative, so the order of the terms in the denominator can be reversed ...
= 2 * S1 * S2 / (S1 + S2)
<spam deleted>

5. Re: How to calculate average based on max denominator
Emily Z Jun 6, 2016 11:38 PM (in response to Mahfooj Khan)Thanks Kahfooj!! This is perfect!
But there's another problem. My actual data has another layer above category (e.g. dept) and I want the Average to be fixed at category level in order to average it again at dept level. Is there a way to achieve this?

6. Re: How to calculate average based on max denominator
Mahfooj Khan Jun 7, 2016 12:12 AM (in response to Emily Z) 
7. Re: How to calculate average based on max denominator
Emily Z Jun 7, 2016 12:42 AM (in response to Mahfooj Khan)Thanks for your time Mahfooj Khan!
Kindly refer to the attached excel file. Raw data in sheet2. What I want to achieve is in sheet1 column R 'Average Order per category' and later I'll use it to compute column S.
I wantted to use below formula in order to get same results as in sheet1 column Q, but I can't use MAX since COUNTD is already aggregated. Pls. help!
{ FIXED [Category]: MAX(IIF(NOT ISNULL(SUM([Amt])),COUNTD([Month Year]),NULL))}

Average (revised).xlsx 21.9 KB


8. Re: How to calculate average based on max denominator
Mahfooj Khan Jun 7, 2016 3:00 AM (in response to Emily Z) 
9. Re: How to calculate average based on max denominator
Mahfooj Khan Jun 7, 2016 3:17 AM (in response to Mahfooj Khan)I've done something similar to your requirement. Have a look I know you play around the compute part. I'm quite busy now so won’t be able to give you the exact output. I know you can get the idea from it.
Note: One more thing kindly make practice to close the thread by marking answer as correct once you get the answer of your original thread before putting new
question in the existing thread. Just a suggestion
Avg and % Total  revised.twbx 37.1 KB


10. Re: How to calculate average based on max denominator
Emily Z Jun 7, 2016 6:52 PM (in response to Mahfooj Khan)This is awesome! Thanks a lot Mahfooj Khan!

11. Re: How to calculate average based on max denominator
Mahfooj Khan Jun 7, 2016 7:25 PM (in response to Emily Z)I'm glad