Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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?

Need Help
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    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,

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thankyou so much. This is a huge huge help.

  • DataNinja777's avatar
    DataNinja777
    Super User
    2 years ago

    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,