A Problem on Data Grouping

[LynnLI]

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 

[Phoebe5776 · Accepted solution]

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.

[LynnLI]

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)))