Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Contributor II

## 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😅

Best Regard!

Lynn Li

Labels (2)

• ### Layout & Visualizations

1 Solution

Accepted Solutions
Contributor

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.

2 Replies
Contributor

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.

Contributor II
Author

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({}sales)>0, if(rangesum(above(sum({}sales)/sum(total {}sales),1,RowNo()))<0.6,'A STOCKS', if(rangesum(above(sum({}sales)/sum(total {}sales),1,RowNo()))<0.9,'B STOCKS', 'C STOCKS')),),Week,(ArticleCode_WeekName,(=SUM({}sales),Desc)))
Community Browser