Numerical Column Issue

[Anonymous]

Hi all, I`m currently working as part of a project converting a large number of Spotfire reports to Power BI. One of the reports has a small number of scatter plots that refer to a column made up of ranges of numbers as shown below:-

Fluid Depth

<200

201-500

501-800

801-1000

>1km

The problem I have is that whilst Spotfire can work with this data in the Y axis, Power BI wont let me drag this column into the Y axis field. Is there a way I can transform these values or get Power BI to work with these kinds of values?

[Anonymous]

Thankyou so much. This is a huge huge help.

[DataNinja777 · Accepted solution]

Hi @Anonymous ,

From your description, I understood that you want to bucket the numerical values in the column instead of the numerical output of a measure, in which case, you can achive your required output by just utilizing the calculated column like below next to your column.  

Fluid Depth =
SWITCH (
    TRUE (),
    'Table'[Value] < 200, "<200",
    'Table'[Value] >= 201
        && 'Table'[Value] < 500, "201-500",
    'Table'[Value] >= 501
        && 'Table'[Value] < 800, "501-800",
    'Table'[Value] >= 801
        && 'Table'[Value] < 1000, "801-1000",
    ">1000"
)

 In order to sort the text values in the numerical bucket order, I've also added the calculated sort column like below.  

# = switch(true(),
'Table'[Value]<200,1,
'Table'[Value]>=201&&'Table'[Value]<500,2,
'Table'[Value]>=501&&'Table'[Value]<800,3,
'Table'[Value]>=801&&'Table'[Value]<1000,4,
5)

Then used summarize function to summarise the bucket grouping table and created a relationship with your fact table. 

I've then done the countrow of the fact table and put in the bucket sorted out by numerical sort order, which happen to show distribution like below.  

I attach an example pbix file. 

Best regards,