11 Replies Latest reply on Sep 20, 2016 7:07 AM by richard.sonkane

# How to calculate average based on max denominator

Hi,

I've been struggling how to average data based on max denominator in the same category. e.g. pls. refer to below table, the result I want is in the column 'Average Order'. i.e. Average for O5 = (1+0.5)/12 because O2 has 12 months of data. Is there any way that Tableau can achieve this? I attached an excel file with formulas. Kindly help!

 Category Order APR-2015 MAY-2015 JUN-2015 JUL-2015 AUG-2015 SEP-2015 OCT-2015 NOV-2015 DEC-2015 JAN-2016 FEB-2016 MAR-2016 Avearge Order % of Total A O2 1.0 1.0 1.0 1.0 0.9 1.0 1.0 0.9 0.8 1.0 0.9 0.7 0.92 88.1% O5 1.0 0.5 0.13 11.9%
• ###### 1. Re: How to calculate average based on max denominator

Is that what you wanted?

Workbook (public version 9.3) has been saved in my public profile. Have a look.

| Tableau Public

Feel free to ask If you've any question.

Mahfooj

• ###### 2. Re: How to calculate average based on max denominator

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

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

1 of 1 people found this helpful
• ###### 4. Re: How to calculate average based on max denominator

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

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

just include department and change the computation of Avg and % Total and check.

Else share the excel including department column with revised avg and % total

Mahfooj

• ###### 7. Re: How to calculate average based on max denominator

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))}

• ###### 8. Re: How to calculate average based on max denominator

You can try this

• ###### 9. Re: How to calculate average based on max denominator

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

1 of 1 people found this helpful
• ###### 10. Re: How to calculate average based on max denominator

This is awesome! Thanks a lot Mahfooj Khan!