topic Calculate with TOPN in DAX Commands and Tips

Calculate with TOPN

_Regina — Wed, 25 May 2022 03:35:19 GMT

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

Re: Calculate with TOPN

amitchandak — Wed, 25 May 2022 07:16:22 GMT

_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/

Re: Calculate with TOPN

_Regina — Wed, 25 May 2022 15:00:55 GMT

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