Forum Discussion

OH's avatar
OH
Regular Visitor
8 years ago
Solved

How to best categorize data based on ranges

Hello all   Recently (some weeks ago) I entered the world of PowerBI for analyzing (big) data. When typing my subject I already read the proposed other articles but non of them really answered my ...
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi OH

    Assume you have a simple dataset with a column which shows the volume of a body.

    First you need create a new table by entering data to show the the Volume range

     

    Second in your dataset, create columns

    category num = IF([volumne]<=0.1,1,IF([volumne]<=0.2,2,IF([volumne]<=0.5,3,IF([volumne]<=1,4))))

    Next create relstionship between your dataset and new created table by [category num].

     

     

     

    Then create a column

     

    range = RELATED(Table1[category range])

     

     

    Best Regards

    maggie

     

     

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

Hi OH

Assume you have a simple dataset with a column which shows the volume of a body.

First you need create a new table by entering data to show the the Volume range

 

Second in your dataset, create columns

category num = IF([volumne]<=0.1,1,IF([volumne]<=0.2,2,IF([volumne]<=0.5,3,IF([volumne]<=1,4))))

Next create relstionship between your dataset and new created table by [category num].

 

 

 

Then create a column

 

range = RELATED(Table1[category range])

 

 

Best Regards

maggie

 

 

 

OH's avatar
OH
Regular Visitor
8 years ago

Hello maggie

 

Thanks for the explanations, they are just the right level for me to reproduce...;-)

 

Apart from the technique of second table for the ranges and the relationsship creation,

the sentence: "Next in your dataset, create columns..." followed by the formula, was basically the eye-opener:

I now realize that somewhere in the process I MUST have a kind of "IF" construct to tell the software, how to categorize.

 

But as you seem to belong to the PowerBI Team maybe this might an enhancement request (feel free to forward it to the right place, if you are not the person in charge for ERs)

 

I believe it is a very common usecase, that data contains values within a min and max (numerical) value.

And often it is helpful to categorize those data by dividing the min/max range in a certain number of "from-to" ranges ("categories") either linear or logaritmic.

 

Now a function/GUI would be desirable that allows the user (after as selection of a column containing values) to select/enter:

  • Number of ranges to divide the total min max range (thus calculating the Stepwidth fo each category)
  • Or Stepwidth (thus calculating the Number of Ranges)
  • linear or logaritmic division of ranges
  • A (semi-automatic) naming Scheme for the categories to be created

and then the function creates a new column with the categories.