measure for classifying sales

Question

Hi&nbsp;I have the following challenge. I have a table with the following fields (among others)&nbsp;&lt;reseller&gt;, &lt;customer&gt;, &lt;units_sold&gt;, &lt;timestamp&gt;&nbsp;I want to classify resellers based on their production and in the context of time. This means that a reseller is&nbsp;Small: if the total sales is between 0-100 unitsMedium: 101 to 500Large: 501 plus&nbsp;and I want to plot a graph with the number of small, medium, large resellers over time (day, month, QTR, year, overall). I suspect I would need three measures that would count the number of resellers in each bin and then graph these.&nbsp;There is a catch though. the units sold, are not the sales in the specific time period but the commulative sales of the partner up until the end of the particular period. For example a parther would appear as small as he starts doing business and will transition to medium and large as he sells.&nbsp;this means that if a customer sells one unit per day, 30 days per month, the first 100days he would appear as small, after than up untill 500days he will be medium and after than large.&nbsp;Thus, I don't want to be only counting the sales of the specific period but his sales up until the end of the said period...&nbsp;thank you in advance&nbsp;Chronis

anonymous · Answer

Hi&nbsp;Anonymous,I think i may find a solution for classifying the reseller,First of all, you can create a new calculate column to calculate the cumulative sum by each Reseller, and then another new condition column to classify the reseller.&nbsp;&nbsp;Here is my dataset:&nbsp;1. I add a Index column to name each row&nbsp;2. New calculated column: Total_Sold to do a cumulative sum&nbsp;Total_Sold = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM(Seller[Unit_Sold]);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ALL(Seller);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Seller'[Row ID] &lt;= EARLIER('Seller'[Row ID])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&amp;&amp; Seller[Reseller] = EARLIER(Seller[Reseller])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ))&nbsp;&nbsp;3. Classify the reseller&nbsp;Hope this can help you ;).&nbsp;Chen

anonymous · Answer

Anonymous&nbsp;wow this is quite informative.&nbsp;&nbsp;thank you very much for the prompt reply. I'll give it a go with my data set and let you know&nbsp;If i understand this correctly, the "total sold" is a new calculated table, right?

anonymous · Answer

Hi&nbsp;Anonymous,&nbsp;You are welcome !&nbsp;Yes, ""Total sold" is the new calculated column, this is the DAX Formule:&nbsp;Total_Sold = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM(Seller[Unit_Sold]);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ALL(Seller);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Seller'[Row ID] &lt;= EARLIER('Seller'[Row ID])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&amp;&amp; Seller[Reseller] = EARLIER(Seller[Reseller])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ))&nbsp;Happy to be helpful&nbsp;:catvery-happy:

anonymous · Answer

Anonymous&nbsp;top man !!!&nbsp;can I pick your brain cause I want to undestand the logic, not just simply apply it.&nbsp;the argument'Seller'[Row ID] &lt;= EARLIER('Seller'[Row ID])&nbsp;asks the expession to "return" rows with an index earlier than the current index, correct? now "Row ID" is an integer so I can understand the stament. then however, you ask the same for the "reseller" field which is a string in the form of the following argument&nbsp;Seller[Reseller] = EARLIER(Seller[Reseller]&nbsp;What does the above achieve? and how can the "EARLIER" statement be relevent to a string column in this case the "reseller"?&nbsp;&nbsp;

anonymous · Answer

Hi Anonymous,&nbsp;I think i konw your confuse, the&nbsp;EARLIER&nbsp;function will always get the value of the column prior to the current table operation.&nbsp;EARLIER&nbsp;succeeds if there is a row context prior to the beginning of the table scan. Otherwise it returns an error.It doesn't metter the type of values.&nbsp;Because the type of value of column Seller[RowID] is numeric, the function EARLIER will return a numeric, so we can use&nbsp;'Seller'[Row ID] &lt;= EARLIER('Seller'[Row ID]) to compare two numbers.&nbsp; And&nbsp;in the Reseller column, the value's type is text, so the EARILIER will return a text.&nbsp;Ref: https://msdn.microsoft.com/en-us/query-bi/dax/earlier-function-dax&nbsp;I don't know if&nbsp;it's clear ;)&nbsp;Chen&nbsp;

Forum Discussion

measure for classifying sales

6 Replies

Related content

Group and Classify Measures in a Calculated Table

Lowest 5 Sales measure

Measure for Same Store Sales Growth

Sales Measure

Measures for half-year sales