- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A Problem on Data Grouping
Hi Buddy,
Each week we sold thousands of articles and would like to group these articles according to three groups:
A STOCKS are top articles accounted for 60% of total sales, B STOCKS accounted for the next 30% of total sales, and the left 10% are C STOCKS.
The problem is the dimension of grouping is only applicable for the total number😂.
If the requirement is to apply this logic to each week, how to remedy the dimension logic as follow:
=aggr(
if(rangesum(above(sum({<Year={"$(=$(vmaxyear))"},Week={"<=$(=Week(today()-7))"}>} sales)/sum(total {<Year={"$(=$(vmaxyear))"},Week={"<=$(=Week(today()-7))"}>} sales),1,RowNo()))<0.6,'A STOCKS',
if(rangesum(above(sum({<Year={"$(=$(vmaxyear))"},Week={"<=$(=Week(today()-7))"}>} sales)/sum(total {<Year={"$(=$(vmaxyear))"},Week={"<=$(=Week(today()-7))"}>} sales),1,RowNo()))<0.9,'B STOCKS',
'C STOCKS')),(ArticleCode,(=SUM({<Year={"$(=$(vmaxyear))"},Week={"<=$(=Week(today()-7))"}>} sales),Desc)))
Attached is the layout result for reference, which is not correct😅.
Looking forward to your answer.
Best Regard!
Lynn Li
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
To apply the A/B/C stock logic on a per week basis, we need to calculate the category thresholds dynamically based on the sales for each week. Here is one way to do it in DAX:
Stock Category =
VAR TotalSales =
SUMX(SUMMARIZE('Sales', 'Sales'[ArticleCode], 'Sales'[Week], "Sales Total", SUM('Sales'[Sales])), [Sales Total])
VAR CumulativeSales =
CALCULATE(
SUM('Sales'[Sales]),
FILTER(ALL('Sales'), 'Sales'[Week] <= MAX('Sales'[Week])),
GROUPBY('Sales'[ArticleCode], 'Sales'[Week])
)
RETURN
SWITCH(
TRUE(),
CumulativeSales <= 0.6 * TotalSales, "A Stocks",
CumulativeSales <= 0.9 * TotalSales, "B Stocks",
"C Stocks"
)
See here: myenvoyair login
This calculates the total sales for each week, then uses a cumulative sum to calculate the running percentage of sales for each article up to that week. We can then categorize based on whether the cumulative percentage is less than 60% or 90% of the total for that week.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
To apply the A/B/C stock logic on a per week basis, we need to calculate the category thresholds dynamically based on the sales for each week. Here is one way to do it in DAX:
Stock Category =
VAR TotalSales =
SUMX(SUMMARIZE('Sales', 'Sales'[ArticleCode], 'Sales'[Week], "Sales Total", SUM('Sales'[Sales])), [Sales Total])
VAR CumulativeSales =
CALCULATE(
SUM('Sales'[Sales]),
FILTER(ALL('Sales'), 'Sales'[Week] <= MAX('Sales'[Week])),
GROUPBY('Sales'[ArticleCode], 'Sales'[Week])
)
RETURN
SWITCH(
TRUE(),
CumulativeSales <= 0.6 * TotalSales, "A Stocks",
CumulativeSales <= 0.9 * TotalSales, "B Stocks",
"C Stocks"
)
See here: myenvoyair login
This calculates the total sales for each week, then uses a cumulative sum to calculate the running percentage of sales for each article up to that week. We can then categorize based on whether the cumulative percentage is less than 60% or 90% of the total for that week.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Finally,
We combined articlecode & Week together to aggregate and came to the correct result.
Here is the dimension in case any user in need:
=aggr(IF(SUM({<Year={"$(=$(vmaxyear))"}>}sales)>0, if(rangesum(above(sum({<Year={"$(=$(vmaxyear))"}>}sales)/sum(total<Week> {<Year={"$(=$(vmaxyear))"}>}sales),1,RowNo()))<0.6,'A STOCKS', if(rangesum(above(sum({<Year={"$(=$(vmaxyear))"}>}sales)/sum(total<Week> {<Year={"$(=$(vmaxyear))"}>}sales),1,RowNo()))<0.9,'B STOCKS', 'C STOCKS')),),Week,(ArticleCode_WeekName,(=SUM({<Year={"$(=$(vmaxyear))"}>}sales),Desc))) |