Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
VPlorim
Helper I
Helper I

grouping by min max of sales

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

 

1 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"
)

column.png

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.

View solution in original post

15 REPLIES 15
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Do you know how to get these results in DAX?

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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"))))))))

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"
)

column.png

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.

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
Community Champion
Community Champion

@VPlorim ,

Try this one:

Dynamic Segmentation for Excel 2010-2013 – DAX Patterns



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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
Community Champion
Community Champion

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. 

VPlorim_0-1607366767341.png

 

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.

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
Community Champion
Community Champion

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

Untitled.png

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 )

Screenshot 2020-12-08 111641.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors