cancel
Showing results for
Did you mean:
Highlighted
Member

## Performance of Top 10 over time

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Performance of Top 10 over time

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
3 REPLIES 3
Community Support Team

## Re: Performance of Top 10 over time

Hi @bo_afk ,

To create the measures as below.

`a = SUM(Table1[sales])`
```k =
VAR k =
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] ) )
)
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Member

## Re: Performance of Top 10 over time

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

Community Support Team

## Re: Performance of Top 10 over time

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)