Combine and display data based on a set range

[BI101]

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!

[Ritaf1983 · Accepted solution]

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 · Accepted solution]

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]

@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 · Accepted solution]

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]

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