# Subtotals on aggregate conditional values

#### martingaleh

##### Board Regular
I can't tell if the image is loaded.
Here's the url:
https://imagebin.ca/v/4DD95zPeoS5p

Anyway, sum is sum(Table1[value])
maxmo is max(xmonth)
measure 1 is if([maxmo]<12,divide([sum]*12,[maxmo],[sum])

How do I get the subtotal at line 1 to equal 48.8 and not 44. I understand why its 44, but I don't have the ability to figure out sumx with an artificial table and the if command etc. etc. just to get the subtotal to work.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### martingaleh

##### Board Regular
I came up with this, but this can't be the answer:
SUMMARIZE(Table1,Table1[Parent],Table1[child]),
"sumz",[sum],"maxmoz",[maxmo]),if([maxmoz]<12,divide([sumz]*12,[maxmoz]),[sumz]))

because it only works with this specific pivot table. Is there a general answer?

#### VBA Geek

##### MrExcel MVP
what you are after should be achieve by this

=<br><span class="Keyword" style="color:#0070FF">SUMX</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">SUMMARIZE</span><span class="Parenthesis" style="color:#969696"> (</span><br>        Table1,<br>        Table1[Parent],<br>        Table1[char]<br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">IF</span><span class="Parenthesis" style="color:#969696"> (</span><br>        [maxmo] < <span class="Number" style="color:#EE7F18">12</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">DIVIDE</span><span class="Parenthesis" style="color:#969696"> (</span><br>            [SUM] * <span class="Number" style="color:#EE7F18">12</span>,<br>            [maxmo]<br><span class="indent8">        </span><span class="Parenthesis" style="color:#969696">)</span>,<br>        [SUM]<br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>

now if your model is different than what you have shown then the answer could be different

Replies
0
Views
594
Replies
2
Views
2K
Replies
2
Views
631
Replies
4
Views
216
Replies
10
Views
2K

1,170,954
Messages
5,872,935
Members
432,952
Latest member
Il Prete

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back