Forum Discussion

BI101's avatar
BI101
Regular Visitor
2 years ago
Solved

Combine and display data based on a set range

Hi,   I would like to display sales data as # of quotes in a monetary bucket. For example: 50 quotes in the range of 0-$50,000 or 20 quotes in the range of $50,000-100,000 I feel like a stacked b...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi BI101 

    To create bins by sales sum you can add a  calculated column like :

    Bins = if('Table'[Sales]>0 && 'Table'[Sales]<= 49999,"0-49,999",
    if('Table'[Sales]>49999 && 'Table'[Sales]<= 100000,"50,000-99,999","100,000+"))

    To sort the bins in the right order on visuals you can create a table with bins' names and sort order :

    create a relationship between the tables :

    Modify sort order :

     

    create simple DAX for distinctount of quotes :

    Quotes# = DISTINCTCOUNT('Table'[Quote id])
    Put the data on the graph:

    PBIX is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi BI101 
    That is why I added a sort order column and a table :

    You need it to sort the bins in the right order

    And use the bins column from this "small" table on visuals 

    More information about sorting by column:

    https://www.techrepublic.com/article/how-to-sort-by-column-power-bi/

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Ritaf1983's avatar
Ritaf1983
Super User
2 years ago

Hi BI101 

To create bins by sales sum you can add a  calculated column like :

Bins = if('Table'[Sales]>0 && 'Table'[Sales]<= 49999,"0-49,999",
if('Table'[Sales]>49999 && 'Table'[Sales]<= 100000,"50,000-99,999","100,000+"))

To sort the bins in the right order on visuals you can create a table with bins' names and sort order :

create a relationship between the tables :

Modify sort order :

 

create simple DAX for distinctount of quotes :

Quotes# = DISTINCTCOUNT('Table'[Quote id])
Put the data on the graph:

PBIX is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

  • BI101's avatar
    BI101
    Regular Visitor
    2 years ago

    Ritaf1983 

    thanks for the help!

    I'm able to do what you mentioned but for some reason my bins arnt sorting in the right order.

    I can also group by and create a table with the qty of quotes per bin but either way i do it, my axis is out of order. Any suggestions for this?

    • Ritaf1983's avatar
      Ritaf1983
      Super User
      2 years ago

      Hi BI101 
      That is why I added a sort order column and a table :

      You need it to sort the bins in the right order

      And use the bins column from this "small" table on visuals 

      More information about sorting by column:

      https://www.techrepublic.com/article/how-to-sort-by-column-power-bi/

      If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

      • BI101's avatar
        BI101
        Regular Visitor
        2 years ago

        Thanks! for some reason i just restarted my powerbi and it worked