Calculation not working in PBI Desktop.

[Anonymous]

Hello,

I have a problem with a calculated column (please see below) that is giving the wrong result for the Net Strategic Value in my PBI table.
The result should be around 40 and not 80.

As an example, I assume if the lowest value in the range of values is -50,000 and the highest +50,000, -25,000 would have a strategic value of 25 as it’s a quarter of the way between.

What can I do to have the correct strategic value for negative numbers?

Net Surplus Sale Strategic Value =

var Max_value = MAX(Costs[Net Surplus Generated from Sale])

var Min_Value = MIN(Costs[Net Surplus Generated from Sale])

return ((Costs[Net Surplus Generated from Sale]-Min_Value) / (Max_value- Min_Value))*100

FYI, I tried the calculated column below, but the result was the same.

Net Surplus Sale Strategic Value =
var Max_value = CALCULATE(MAX(Costs[Net Surplus Generated from Sale]), ALL())
var Min_Value = CALCULATE(MIN(Costs[Net Surplus Generated from Sale]), ALL())
return ((Costs[Net Surplus Generated from Sale]-Min_Value) / (Max_value- Min_Value))*100

Many thanks!

[amitchandak]

@Anonymous ,  This need to be a measure like

Net Surplus Sale Strategic Value =
var Max_value = CALCULATE(MAX(Costs[Net Surplus Generated from Sale]), ALL())
var Min_Value = CALCULATE(MIN(Costs[Net Surplus Generated from Sale]), ALL())
 
return Divide(Sum(Costs[Net Surplus Generated from Sale])-Min_Value, (Max_value- Min_Value))*100

The first calculation you have given can work as new column

[Anonymous]

Thanks for your reply @amitchandak 
But I've tried it a measure and I get exactly the same result. 

[amitchandak]

@Anonymous ,

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Unfortunately, I don't have permission to share pbix files here. 

[Anonymous]

Hi @Anonymous,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Notice: you can upload to the network drive and share links here or paste the dummy data on your report in table format.

Regards,

Xiaoxin Sheng