Scaled Value Categorization Measure

[neil37]

Hello,

I want to preface by saying that you for your help.

I currently have a working measure that calculates a “scaled value” based on a total number. That scale ranges from 1 to 0.

Object	Score	Scaled Value
Oranges	900	1
Bananas	800	.45
Apples	500	.27
Peaches	200	0

ScaledValue =

VAR _Min = Calculate(MIN(cTbl_TotalScoreCalculate[Totaled Score]),REMOVEFILTERS())

VAR _Max = Calculate(MAX(cTbl_TotalScoreCalculate[Totaled Score]),REMOVEFILTERS())

RETURN

IF(

    HASONEVALUE ( cTbl_TotalScoreCalculate[name_id]),

    DIVIDE ( SUM(cTbl_TotalScoreCalculate[Totaled Score] ) - _Min, _Max - _Min))

I am hoping to essentially create a measure that categorizes the scaled values based on its score as it falls within a range. I’d like the measure to be able to show the Category Column below:

Object	Score	Scaled Value	Category
Oranges	900	1	Cat 1
Bananas	800	.45	Cat 2
Apples	500	.27	Cat 3
Peaches	200	0	None

I would like the categories to fall within the following ranges:

Cat 1: >= .50
Cat 2: >=.37 & <.50

Cat 3: >= .24 < .37
None: <.24

Thank you for your assistance!!

[rsbin · Accepted solution]

@neil37 ,

I would like to suggest using the SWITCH function:

Category = SWITCH(
                TRUE(),
                [Scaled Value] >= .5, "Cat 1",
                [Scaled Value] >= .37, "Cat 2",
                [Scaled Value] >= .24, "Cat 3",
                 "None" )

I believe by ensuring the values are in descending order, this should work.  My syntax might be a little off, but I think you should be able to get it going.

Good Luck and Regards,

[neil37]

Worked perfectly. Thank you!!!