TOPN function in CALCULATE

[powerbiuser9999]

Hi

I have an interesting problem when trying to create a measure in PBI Desktop.

I have data thats akin to this, which I have used SUMMARIZE to create categories and a count of their respective sales.

Category      COUNT of sales
Apples          32
Kiwi              46

Bananas       65

Oranges       23

Dragon         87

Grapes         54

Mango        126

Cherry         84

There are 8 categories, but I am specifically looking to SUM the Top 5 categories by the amount of the COUNT.

So in my example, I am looking for a measure which will allow me to SUM Mango,Dragon,Cherry,Bananas and Grapes. My expected response is 416 in this example.

Whilst I can easily SUMMARIZE my data and apply the count, I am struggling with how to SUM the specific Top 5 by the COUNT.

Can anyone assist please?

[v-zhangti · Accepted solution]

Hi, @powerbiuser9999 

You can try the following methods.

1. Create a new calculation column to rank the COUNT of sales.

Rank = 
RANK.EQ ( [COUNT of sales], 'Table'[COUNT of sales], DESC )

    2. Create a measure to sum the top five.

Measure =
CALCULATE (
    SUM ( 'Table'[COUNT of sales] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Rank] <= 5 )
)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more.

[powerbiuser9999]

@v-zhangti Charlotte. You star! Thank you!

[powerbiuser9999]

Hi @YukiK 

Thanks so much for your reply.

This hasn't worked for scenario, but i may have worded this incorrectly.

I am going to start a new question which is better worded, but thank you so much for your time.

[YukiK]

Try a measure like this one:

TOP5 sum of count of sales =
CALCULATE( SUM('Table'[ COUNT of sales] ),
TOPN (
5,
ADDCOLUMNS (
VALUES ( 'Table'[Category     ] ),
"@Count of Sales", CALCULATE( SUM('Table'[ COUNT of sales] ) )
),
[@Count of Sales],
DESC
)
)

Please give it a thumbs up if this helps!

[tboonhau]

Hi @YukiK , this solution works. Arigato!