Performance of Top 10 over time

[bo_afk]

Hi,

In my data I have category name, sales, and week number. For each week, I would like to calculate the total sales for the top 10 categories and then see this trend over time. 

Does anyone know if this is possible?

Thanks

afk

[v-frfei-msft]

Hi @bo_afk ,

To create the measures as below.

a = SUM(Table1[sales])

k = 
VAR k =
    ADDCOLUMNS (
        Table1,
        "ranka", RANKX ( ALLEXCEPT ( Table1, Table1[week number] ), [a] )
    )
VAR kf =
    FILTER ( k, [ranka] <= 10 )
RETURN
    CALCULATE ( SUM ( Table1[sales] ), KEEPFILTERS ( kf ) )

Measure = 
CALCULATE (
    SUMX ( Table1, [k] ),
    FILTER ( ALL ( Table1 ), Table1[week number] <= MAX ( Table1[week number] ) )
)

[bo_afk]

Hi @v-frfei-msft , thanks for your response.

I tried your measures but it didn't seem to work for my data. Perhaps I didn't explain it properly.

See below a sample data set. For each week, in this example, I want to sum the sales for the top 2 categories. In Week 1 these are categories B and C with a total of 35 (20+15) and then in Week 2 these are categories A and C with a total of 31 (20+11)

Sample data					Desired Output
Week	Category	Sales	Rank		Top 2 total
Week 1	A	10	3		Week 1	35
Week 1	B	15	2		Week 2	31
Week 1	C	20	1
Week 2	A	20	1
Week 2	B	7	3
Week 2	C	11	2

[v-frfei-msft · Accepted solution]

Hi @bo_afk ,

To create measures as below.

sum = SUM(Table1[Sales])

Measure = var ran = RANKX(ALLEXCEPT(Table1,Table1[Week]),[sum],,DESC,Dense)
var k =FILTER(Table1,Table1[Rank]<=2)
return
CALCULATE([sum],KEEPFILTERS(k))