Can Grand Total in Pivot Table display beginning value instead of sum?

[bigck2]

Hello community,

I have a couple of Calculated Fields:

[Budget - Beg Date] := MIN( Fact_Budget_Occ[Month] )


[Budget - Beg Occupied Units] := CALCULATE( SUM( Fact_Budget_Occ[Beginning_Occupied_Units] ) , FILTER( Fact_Budget_Occ,
Fact_Budget_Occ[Month] = [Budget - Beg Date]
)
)

For this Field [Budget - Beg Occupied Units] it works except in the Grand Total Column it sums up the values across the row, and I'd like it to pull the value from the first date/month.

[/IMG]




Is there a way to make this field behave this way?

Thanks,

Chris

 

[FranzV]

Try

[Budget - Beg Occupied Units] :=
CALCULATE (
    SUM ( Fact_Budget_Occ[Beginning_Occupied_Units] ),
    Fact_Budget_Occ[Month] = MIN ( Fact_Budget_Occ[Month] )
)

 

[bigck2]

Thanks for the suggestion FranzV.

I tried, but when I tried to use that formula, and then check the formula I got this error message:

Calculation error in measure 'Budget_Occ'[Budget - Beg Occupied Units]: A function 'MIN' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

[FranzV]

Maybe adding FILTER()

[COLOR=#000000][Budget - Beg Occupied Units][/COLOR] :=
[COLOR=#0000ff]CALCULATE[/COLOR] (
    [COLOR=#0000ff]SUM[/COLOR] ( [COLOR=#000000]Fact_Budget_Occ[Beginning_Occupied_Units][/COLOR] ),
    [COLOR=#0000ff]FILTER[/COLOR]( 
        Fact_Budget_Occ,
        [COLOR=#000000]Fact_Budget_Occ[Month][/COLOR] = [COLOR=#0000ff]MIN[/COLOR] ( [COLOR=#000000]Fact_Budget_Occ[Month][/COLOR] )
    )
)