Feature Scaling using Dax

[Polly]

Hi,

I wonder if any one can help.

I pretty new to powerBI and Dax and I am struggling to normalsie a variable using the Feature Scaling Method. I am trying the below but it is bring up the following error message, even though all instances of the column are agregated

A single value for column 'Cancellation Rate Indexed' in table 'Agent Lookup' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.  

Feature Scaling for Cancellation Rate =

(SUM('Agent Lookup'[Cancellation Rate Indexed])-MIN('Agent Lookup'[Cancellation Rate Indexed],ALL('Agent Lookup')))

/

(MAX('Agent Lookup'[Cancellation Rate Indexed],ALL('Agent Lookup'))-MIN('Agent Lookup'[Cancellation Rate Indexed],ALL('Agent Lookup')))

Thanks

[v-shex-msft · Accepted solution]

HI @Polly,

Based on your formula. I think you want to use current value to deal with max/min value from current table, right?

If this is a case, you should use calculate or minx/maxx function to apply the filter for this formula.

Result =
DIVIDE (
 SUM ( 'Table'[Amount] ) - MINX ( ALL ( 'Table' ), 'Table'[Amount] ),
 MAXX ( ALL ( 'Table' ), 'Table'[Amount] )
 - MINX ( ALL ( 'Table' ), 'Table'[Amount] )
)

Regards,

Xiaoxin Sheng

[Polly]

Thanks!

[Polly]

Thanks!

[TomMartens]

Hey,

on 1st look you should define your measure like this

your measure = 
(
SUM('Agent Lookup'[Cancellation Rate Indexed])-
CALCULATE(
  MIN('Agent Lookup'[Cancellation Rate Indexed])
  ,ALL('Agent Lookup')
)
)

/
(
CALCULATE(
  MAX('Agent Lookup'[Cancellation Rate Indexed])
  ,ALL('Agent Lookup')
)
-
CALCULATE(
  MIN('Agent Lookup'[Cancellation Rate Indexed])
  ,ALL('Agent Lookup')
)
)

Basically you also can store the MIN and MAX values to a variable and then use these variables in the expression, this not just improve readibility but also improve performance

Hope this helps

Regards

Tom