total row in table show average instead total

[EliranMoshe]

hi.

i have a table that calculate monthly data

i have a column the make a average of area data.

in the total row it's show the average instead a total

the expression of the column is

sum( aggr( sum({<Is_Monitor_Active={1},[current_Month?]={0},Customer_Name=,Year={'$(vSelectYear)'}>}total <Site_Area,Year,Month> Total_Yield),Site_Area,Month))
/
Sum( aggr(sum ({<Is_Monitor_Active={1},[current_Month?]={0},Customer_Name=,Year={'$(vSelectYear)'}>}total <Site_Area,Year,Month> Site_Size_DC),Site_Area,Month))

[Or · Accepted solution]

In a table, you can change the Total Settings for your measure:

In a pivot table, you'll have to refactor your code to allow for totals. You can probably do this by using the Dimensionality() function to find out if you're at row level or (sub)total level and use different formulas for each one:

if(Dimensionality()<1,TotalFormula,LineFormula)

Alternatively, you could rewrite your formula to handle both cases by wrapping the whole thing in e.g. sum(aggr(YourFormula),YourDimension1,YourDimension2), not sure how that'll interact with your already-complex formula insofar as getting the correct data and performance.

[EliranMoshe]

thanks , as you can see i have no option to change the total function

also at the other columns the total is good

[Or]

As I said, that option is only available for regular tables, and you appear to be using a pivot table. In pivot tables, the total function is always the equivalent of Auto - it will be calculated using the same formula as the lines. You can use one of the approaches in my previous post to work around this.

[EliranMoshe]

many thanks . it's work perfectly