Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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.
Sorry, sir. I gave you an M (Power Query) solution, that ain't DAX!!
Do you know how to get these results in DAX?
Hey there. No reason you cannot attach a slicer to this column.
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
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 tried this and it is giving me errors. Any idea what may be wrong
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.
I get this far then it turns red.
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.
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.
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.
@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 )
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! |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
14 | |
13 | |
12 | |
11 |