Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
_Regina
Helper I
Helper I

Calculate with TOPN

I have a table like this

TableA

 

PromotionCommissionSale dateSale IDProduct ID
PromoA1021 Dec 20211231
PromoA1029 Dec20211241
PromoA85 Jan 20221252
PromoB1516Nov 20211222

 

Table B

Product IDProduct Description
1TV
2Camera

 

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 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors