Forum Discussion

neil37's avatar
neil37
Advocate I
4 years ago
Solved

Scaled Value Categorization Measure

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!!

Need Help
  • rsbin's avatar
    rsbin
    4 years ago

    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,

2 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    4 years ago

    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,