Subtract Table / Column Value from Measure Value?

[BarryFletcher]

Hi all,

I have a value set in a measure for Allocated Hours. I am looking to subtract the Total Hours column (as filtered by a slicer) from that value but it keeps telling me the table / column are not found? Any help would be appreciated.

[DataInsights · Accepted solution]

@BarryFletcher,

You need to wrap Table1[Total Hours] in an aggregate function such as SUM. I would create the measure below, and then the New Balance measure can be [Allocated Hours] - [Total Hours].

Total Hours = SUM ( Table1[Total Hours] )

[BarryFletcher]

Total Hours = SUM ( Table1[Total Hours] )

Is there a way to fileter the above so that if only sums certain values / ros in the Total Hours column? 

[DataInsights · Accepted solution]

@BarryFletcher,

You can use the CALCULATE function to control filter context. I prefer to keep base measures free of filters so they can be used as building blocks for other measures. Thus, you could have a measure like this:

Total Hours Filtered = CALCULATE ( [Total Hours], FilterCondition1, FilterCondition2 )

[BarryFletcher]

Thanks for the help - very much appreciated.

[DataInsights · Accepted solution]

@BarryFletcher,

You need to wrap Table1[Total Hours] in an aggregate function such as SUM. I would create the measure below, and then the New Balance measure can be [Allocated Hours] - [Total Hours].

Total Hours = SUM ( Table1[Total Hours] )

[BarryFletcher]

Excellent - worked a treat - thank you