I want the Total/Subtotal percent to be a calculation of the values in the Totals/Subtotal

[Anonymous]

Im trying to create a forecast accuracy matrix table with multiple slicers to select region/partner/Market by month.

Im able to get the correct formula into the Forecast Accuracy column, but when we get to the total / subtotal rows, the forecast accuracy percentage is either summing / averaging the column.

I want to take the values from the total/subtotal row and apply the formula to obtain the correct percentage.

I've attached an example data set for reference. 

Many 'solutions' Ive found would help me with percentage of total/subtotal, so not been able to make them work.

Any help will be most appreciated.

Google Drive Link to PBI Report 

[ToddChitt · Accepted solution]

Looks like your [Forecast Accuracy] is defined as a COLUMN. You should re-write it as a MEASURE. And that measure should be based on SUM Aggregations of the Numerator and Denominator. Oh, and I use the DIVIDE DAX function:

Measures:

TOTAL Error = SUM('DUMMY February'[Error])

TOTAL Actual = SUM('DUMMY February'[Actuals])

Forecast Accuracy 3 = 1 - DIVIDE ( [TOTAL Error] , [TOTAL Actual], 0 )

I leave it to you to wrap in the negative handling.