Calculation subtotal with if function

Question

I have the following formula in a separate table to the below (below table is data set).

However the results for the section are out by one section

e.g. How can I get the licensing result in the cash?

=AVERAGE(IF(Table1[[#All],[SECTION_NAME]]=[@[SECTION_NAME]],IF(SUBTOTAL(102,OFFSET($G$12,ROW(Table1[RC - 1 RESULTS])-ROW(G$12),0)),Table1[RC - 1 RESULTS])))

I resolved it already, the expected output is the section averages done as a subtotal so I can filter the main table and the formula updates to give the average for the sections with visible rows. The table is a data set which is hidden, on the report page they just see a small table with section names (like the top right corner) they then select slicers for AREA, REGION and SITE to get the dynamic average. Thanks for looking, its doing what i need but not sure its the right way :) — Hoube78, Jun 27 '16 at 23:11

score 0 · Accepted Answer · edited Jun 27 '16 at 23:16

So in case anyone ever has the same issue, I found my own answer by using the array formula below:

=AVERAGE(IF(Table1[SECTION_NAME]=@[SECTION_NAME]],IF(SUBTOTAL(102,OFFSET($G$13,ROW(Table1[RC - 1 RESULTS])-ROW(G$13),0,1)),Table1[RC - 1 RESULTS])))

Don't forget to enter it as an array by selecting the formula and holding Ctrl and Shift and pressing Enter.

Hope this helps someone else :)

J.

Calculation subtotal with if function

1 Answers1