Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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
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
Thanks!
Thanks!
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |