cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate with TOPN

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
1 ACCEPTED SOLUTION
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/

New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!
2 REPLIES 2
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/

New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!
Regular Visitor

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

Announcements