grouping by min max of sales

[VPlorim]

I need to create a min/max table of sales to report on.  For example i need to create group a 0-249 , group b 250-1000, group c 1001-5000 etc.  I would like to use these groupings as filters as well.

Is there a way for me to create this custom min/max groupings to use as a slicer to be interactive with other visuals on the page?
Thank you

[watkinnc]

Sorry, sir.  I gave you an M (Power Query) solution, that ain't DAX!!

[VPlorim]

Do you know how to get these results in DAX?

[watkinnc]

Hey there.  No reason you cannot attach a slicer to this column.

[VPlorim]

I have tried to add a column of sales for groups and I am getting an error with this. Am I doing something wrong?

I tried this and it is giving me errors.  Any idea what may be wrong

Sales Grouops = 'Total Sales 2020'[TotalSV](ADDCOLUMNS('Total Sales 2020',"Sales Groups", IF('Total Sales 2020'[TotalSV] > 5000 = "5000+", IF('Total Sales 2020'[TotalSV]>2500 = "2500-5000", IF('Total Sales 2020'[TotalSV]>1001 = "1001-2500", IF('Total Sales 2020'[TotalSV]>250 = "250-1000", IF('Total Sales 2020'[TotalSV]> 1 = "1-249", IF('Total Sales 2020'[TotalSV] < 1 = "0"))))))))

[watkinnc]

You can add a column like so:

= Table.AddColumn(TableOrStepName, "NewColumnName", each if [Sales] > 10000 then "10000+" else if [Sales] > 5000 then "5000-1000" else if [Sales] > 2500 then "2500-5000" else if [Sales] > 1000 then "1000-2500" else if [Sales] > 500 then "500-1000" else if [Sales] > 250 then "250-500" else "Less than 250")

Then you can use that column as a filter. 
--Nate

[VPlorim]

I tried this and it is giving me errors.  Any idea what may be wrong

Sales Grouops = 'Total Sales 2020'[TotalSV](ADDCOLUMNS('Total Sales 2020',"Sales Groups", IF('Total Sales 2020'[TotalSV] > 5000 = "5000+", IF('Total Sales 2020'[TotalSV]>2500 = "2500-5000", IF('Total Sales 2020'[TotalSV]>1001 = "1001-2500", IF('Total Sales 2020'[TotalSV]>250 = "250-1000", IF('Total Sales 2020'[TotalSV]> 1 = "1-249", IF('Total Sales 2020'[TotalSV] < 1 = "0"))))))))

[v-yingjl · Accepted solution]

Hi @VPlorim ,

Based on your formula, you may need a calculated column like this:

Group =
SWITCH (
    TRUE (),
    'Table'[Sales] > 5000, "5000+",
    'Table'[Sales] > 2500, "2500-5000",
    'Table'[Sales] > 1001, "1001-2500",
    'Table'[Sales] > 250, "250-1000",
    'Table'[Sales] > 1, "1-249",
    'Table'[Sales] < 1, "0"
)

ADDCOLUMNS() is used to create a new calculated table with new columns based on the source table.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VPlorim]

I get this far then it turns red. 

Sales Grouops = 'Total Sales 2020'[TotalSV](ADDCOLUMNS('Total Sales 2020',"Sales Groups", IF('Total Sales 2020'[TotalSV]>5000 then "5000+" else

the then "5000+ else is what is red

[camargos88]

@VPlorim ,

Try this one:

Dynamic Segmentation for Excel 2010-2013 – DAX Patterns

[VPlorim]

I have created an excel min/max and brought that into power bi then made this following measure.  It does not seem to be working.

Sales Range = CALCULATE([Business Volumme Total], FILTER(VALUES('Min Max'[Group]),COUNTROWS(FILTER( 'Min Max', 'Min Max'[Group]>='Min Max'[Min]&&'Min Max'[Group]<='Min Max'[Max]))))

I just do not know what I am doing wrong. I need this to tie to the business volume total and work as a slicer.

[camargos88]

@VPlorim ,

Check these links:

Customers Grouped by the Count of their Orders – Dynamic Segmentation in Power BI Using DAX Measures...

Dynamic Segmentation: How To Segment Customers Into Groups Using Advanced DAX | Enterprise DNA

Dynamic segmentation – DAX Patterns

[VPlorim]

I am looking at the links and I am not seeing instructions on how to create the table with min/max values.

Is there a step by step for this somewhere?  I am looking to create a table like the one below. 

[v-yingjl]

Hi @VPlorim ,

Not certain what is your expected output. Did you want to create a group table with group A,B,C.. (when value is 0-100 group A etc.) or a group table with min value and max values to defien group or anything else?

In your posted picture, What dis the Min Sales and Max Sales represent? Did these fields all come from the data table?

Could you please consider sharing a sample file without any sesentive information or more details about this issue for further discussion?

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VPlorim]

Good Morning,

What I have been tasked to do is to have a grouping of total sales 0-249, 250-1000, 1001-2500, 2501-5000, 5000+  so a total sales volume of the sales people we have for my company.  I want the grouping to be a slicer to interact with details of the sales.  For example if I had this grouping in a slicer I would select the 1001-2500 for example and a table created it would show the people that have had total sales within that amount (1001-2500).  What I have available is either 2 things, I have the entire individual orders table from the database that shows each order by customer # or I have a sql that I can bring in that sums the amounts of the customer sales for a specific date range.  The problem that I am having is getting the grouping to interact with the other tables that I have so I can create charts to odisplay the data based on the selection in the group that I choose.

I can send you an excel sheet of what I am referring to if that will help. 

Thank you so much for taking the time to assist.

[CNENFRNL]

@VPlorim , a little trick to use such a simplified segmenting table (named Segmt) to map a total volume,

Lvl = 
VAR __total_volume = 500 //[Business Volumme Total]
VAR __lv =
    MINX ( FILTER ( SEGMT, SEGMT[Threshold] >= __total_volume ), SEGMT[Threshold] )
RETURN
    CALCULATE ( MAX ( SEGMT[Segment] ), SEGMT[Threshold] = __lv )