Forum Discussion
measure for classifying sales
Hi 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.
Here is my dataset:
1. I add a Index column to name each row
2. New calculated column: Total_Sold to do a cumulative sum
Total_Sold = CALCULATE(
SUM(Seller[Unit_Sold]);
FILTER(
ALL(Seller);
'Seller'[Row ID] <= EARLIER('Seller'[Row ID])
&& Seller[Reseller] = EARLIER(Seller[Reseller])
))
3. Classify the reseller
Hope this can help you ;).
Chen
Anonymous wow this is quite informative.
thank you very much for the prompt reply. I'll give it a go with my data set and let you know
If i understand this correctly, the "total sold" is a new calculated table, right?
Hi Anonymous,
You are welcome !
Yes, ""Total sold" is the new calculated column, this is the DAX Formule:
Total_Sold = CALCULATE(
SUM(Seller[Unit_Sold]);
FILTER(
ALL(Seller);
'Seller'[Row ID] <= EARLIER('Seller'[Row ID])
&& Seller[Reseller] = EARLIER(Seller[Reseller])
))Happy to be helpful :catvery-happy:
Anonymous top man !!!
can I pick your brain cause I want to undestand the logic, not just simply apply it.
the argument
'Seller'[Row ID] <= EARLIER('Seller'[Row ID])
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
Seller[Reseller] = EARLIER(Seller[Reseller]
What does the above achieve? and how can the "EARLIER" statement be relevent to a string column in this case the "reseller"?
Hi Anonymous,
I think i konw your confuse, the EARLIER function will always get the value of the column prior to the current table operation. EARLIER 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.
Because the type of value of column Seller[RowID] is numeric, the function EARLIER will return a numeric, so we can use 'Seller'[Row ID] <= EARLIER('Seller'[Row ID]) to compare two numbers. And in the Reseller column, the value's type is text, so the EARILIER will return a text.
Ref: https://msdn.microsoft.com/en-us/query-bi/dax/earlier-function-dax
I don't know if it's clear ;)
Chen