Calculate with TOPN

[_Regina]

I have a table like this

TableA

Promotion	Commission	Sale date	Sale ID	Product ID
PromoA	10	21 Dec 2021	123	1
PromoA	10	29 Dec2021	124	1
PromoA	8	5 Jan 2022	125	2
PromoB	15	16Nov 2021	122	2

Table B

Product ID	Product Description
1	TV
2	Camera

I need to calculate total commission by Promo for first 10 sales where product category is TV and sale date is between 1 Sept 2021 to 31 Dec 2021 and where Commission is not zero.

I wrote the measure as

Switch(Promotion,"PromoA",

CALCULATE (

SUM ( [Commissions] ),

DATESBETWEEN (

[Sale date],

DATE ( 2021, 09, 01 ),

DATE ( 2021, 12, 31 )

),

 TOPN(

 10,

 TableA,

TableA[Sale ID],

 DESC

),

filter(TableA ,related(TableB[Product Description])="TV"

),TableA[Commissions]<>0)

This gives me blank But if I remove the TOPN clause it gives me correct result. what should I do to get the correct results with TOPN.

I would really appreciate your guidance here.

Thanks 

[amitchandak · Accepted solution]

@_Regina , Try like

Measure =
Var _1 =
CALCULATE (
SUM ( [Commissions] ),
DATESBETWEEN (
[Sale date],
DATE ( 2021, 09, 01 ),
DATE ( 2021, 12, 31 )
),filter(TableA ,related(TableB[Product Description])="TV" && TableA[Commissions]<>0))
return
Calculate( _1, TOPN(
10,
allselected(TableA[Sale ID]), _1
DESC
), Values(TableA[Sale ID]) )

not clear on need to switch

refer

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

[_Regina]

Thank you . Could you help me understand why your solution works and not mine ?