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 bar chart could be good for this but i'm not sure how to combine the data into the ranges.

Any ideas?

 

Thanks!

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

4 Replies

  • 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