Hi, Thanks in advance.I couldn't find a way of uploading a file to the forum. The link below is a google drive link to a copy of the file.https://drive.google.com/file/d/150YjoWvugCp6-dEOnypDtCqF1J9Pkazy/view?usp=sharingIn a previous post:-https://community.fabric.microsoft.com/t5/Desktop/Use-Previous-Value-In-Column-In-Calculation/m-p/3264143#M1094375I was kindly supplied with a measure to do the first calculation I need. Measure = var _a = ALLSELECTED('Table'[Work Type])var _b = MAX('Table'[Row])return SUMX(FILTER(ALL('Table'),[Work Type] in _a&&[Row]<=_b),'Table'[Amount]-'Table'[Available]) Giving the results in the example belowrow 1 Measure is Amount - Available (12 - 2160 = -2148.00)row 2 Measure is Row 1 Measure (-2148) + Row 2 Amount (85) - Row 2 Available (2160) = -4223row 3 Measure is Row 2 Measure (-4223) + Row 3 Amount (1660) - Row 3 Available (2106) = -4723 I need an additional column which deals diffrently with negatives in the measure column. Therefore the 'New Measure' column instead of -negative numbers uses a 0 and only uses positive numbers in the calculation. See illustration below. I hope this makes sense.Thanks Pete

That's unfortunately exactly the point. The result cannot be modified by filters.

PBE

Helper II

3 years ago

Solved

Measure to Deal with Negatives in Calculation

Hi, Thanks in advance.

I couldn't find a way of uploading a file to the forum. The link below is a google drive link to a copy of the file.

https://drive.google.com/file/d/150YjoWvugCp6-dEOnypDtCqF1J9Pkazy/view?usp=sharing

In a previous post:-

https://community.fabric.microsoft.com/t5/Desktop/Use-Previous-Value-In-Column-In-Calculation/m-p/3264143#M1094375

I was kindly supplied with a measure to do the first calculation I need.

Measure =

var _a = ALLSELECTED('Table'[Work Type])

var _b = MAX('Table'[Row])

return SUMX(FILTER(ALL('Table'),[Work Type] in _a&&[Row]<=_b),'Table'[Amount]-'Table'[Available])

Giving the results in the example below

row 1 Measure is Amount - Available (12 - 2160 = -2148.00)

row 2 Measure is Row 1 Measure (-2148) + Row 2 Amount (85) - Row 2 Available (2160) = -4223

row 3 Measure is Row 2 Measure (-4223) + Row 3 Amount (1660) - Row 3 Available (2106) = -4723

I need an additional column which deals diffrently with negatives in the measure column. Therefore the 'New Measure' column instead of -negative numbers uses a 0 and only uses positive numbers in the calculation. See illustration below.

I hope this makes sense.

Thanks

Pete

Need Help

lbendlin
3 years ago
That's unfortunately exactly the point. The result cannot be modified by filters.

Forum Discussion

Measure to Deal with Negatives in Calculation

4 Replies

Related content

DAX Calculated Measure

IF Negative value

Method to calculate cumulative negative balances

Remove negatives

Measure or Calculated Column