cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frenchtom811
Helper I
Helper I

Analyzing sales data by quote size

Hi folks, I must be having a brain fart today because my question seems so basic. 

 

I have transactional level sales data that looks like any other sales data - customer names, line item numbers, regional info, sales, discounts, profit, item number, etc.  The data are broken out into line item level of detail meaning I have multiple rows of data for each sales order number.  All I want to do is analyze my data for sales orders that are below $1,000,000 total value.  Then I want to analyze my data for sales orders that are between $1,000,000 and $5,000,000.  Lastly I want to analyze my data for sales orders that are greater than $5,000,000.  What is the easiest way to do this?  I do not need enything exotic.   

 

Putting Sales Order# in a filter and filtering the Sales Order# on sum of Total Revenue = "what ever value I need" would do the trick.

 

Thanks! 

1 ACCEPTED SOLUTION

Hello @amitchandak , I figured it out!  I created a calculated column with the following code

 

Quote Size = Calculate(Sum(Table[Quote $)]), ALLEXCEPT(Table,Table[Quote ID]))
 
From this column I can create a second column that uses a nested IF statement to define my bin sizes.  Its primative but effective. 

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@Frenchtom811 , if you want to bucket data after adding up, means bucket on the measure you need to do dynamic segmentation or binning

Refer my Video: https://youtu.be/CuczXPj0N-k

or

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hello @amitchandak and thanks for your response.  These solutions look promising but also very complicated.  All I am really trying to do is group my sales orders into 3 categories as described in the original post.   Is there a simple DAX expression I can write that will group my sales order numbers into one of 3 categories based on total sales order value?  I need to slice and filter on these 3 groups so that I can analyze the sales order profiles (customer types, products, profit, etc.)  Creating a separate bin table that has no relation to my sales table means that I cannot filter/slice my sales table using bins defined in the bin table.   

@Frenchtom811 , if you need bucket on the measure (means after aggregating the data) , I doubt there is not simple way.

 

If it is on the line level data, we can always create a new column 

 

New bucket column =

Switch( True() ,

[sale] < 50000, "  less than 50K",

[sales] <100000, " from 50K to 100K", 

//you can add more 

"Others" //default

)

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hello @amitchandak , I figured it out!  I created a calculated column with the following code

 

Quote Size = Calculate(Sum(Table[Quote $)]), ALLEXCEPT(Table,Table[Quote ID]))
 
From this column I can create a second column that uses a nested IF statement to define my bin sizes.  Its primative but effective. 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors