Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BigAl
Frequent Visitor

Numerical Column Issue

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?

1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

Hi @BigAl ,

 

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. 

DataNinja777_0-1718429112501.png

 

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.  

DataNinja777_1-1718429142745.png

I attach an example pbix file. 

Best regards,

 

 

View solution in original post

2 REPLIES 2
BigAl
Frequent Visitor

Thankyou so much. This is a huge huge help.

DataNinja777
Super User
Super User

Hi @BigAl ,

 

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. 

DataNinja777_0-1718429112501.png

 

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.  

DataNinja777_1-1718429142745.png

I attach an example pbix file. 

Best regards,

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.