Forum Discussion

Polly's avatar
Polly
Helper I
8 years ago
Solved

Feature Scaling using Dax

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

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

4 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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