Forum Discussion
TOPN not working in calculated table
Hi,
I created a calculated table grouping the sales units by customer name. From there, I selected the top 50 using the TOPN function. This is the measure:
TOP 50 ShippedSales FYE19 to Present Grouped by Customer, Sum of Units =
TOPN(50,
SUMMARIZE(
FILTER(
ShippedSales,
ShippedSales[Billing Doc Date] >= DATE(2018,7,1) && ShippedSales[Billing Doc Date] <= DATE(YEAR(TODAY()), MONTH(TODAY()),DAY(TODAY()))
),
ShippedSales[Customer Name],
"Sales Units Sum", SUM(ShippedSales[Qty])
)
)
This filters for 50 but the the sales units are quite low, the customers and values don't make sense in context of shipments. So I removed the TOPN part of the measure:
ShippedSales FYE19 to Present Grouped by Customer, Sum of Units =
SUMMARIZE(
FILTER(
ShippedSales,
ShippedSales[Billing Doc Date] >= DATE(2018,7,1) && ShippedSales[Billing Doc Date] <= DATE(YEAR(TODAY()), MONTH(TODAY()),DAY(TODAY()))
),
ShippedSales[Customer Name],
"Sales Units Sum", SUM(ShippedSales[Qty])
)
This calculation works (the values are much higher and top dealers are included in this table). I'm unsure why the TOPN doesn't work for my initial calculation, I'm unsure how to select the for the top 50 from this correct/2nd measure.
Hi Anonymous
You don't seem to be specifying the column to sort by. Try:
TOP 50 ShippedSales FYE19 to Present Grouped by Customer, Sum of Units = TOPN ( 50, SUMMARIZE ( FILTER ( ShippedSales, ShippedSales[Billing Doc Date] >= DATE ( 2018, 7, 1 ) && ShippedSales[Billing Doc Date] <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) ) ), ShippedSales[Customer Name], "Sales Units Sum", SUM ( ShippedSales[Qty] ) ), [Sales Units Sum] )You might have to specify the sort order (ASC, DESC)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
2 Replies
- AlB
Community Champion
Hi Anonymous
You don't seem to be specifying the column to sort by. Try:
TOP 50 ShippedSales FYE19 to Present Grouped by Customer, Sum of Units = TOPN ( 50, SUMMARIZE ( FILTER ( ShippedSales, ShippedSales[Billing Doc Date] >= DATE ( 2018, 7, 1 ) && ShippedSales[Billing Doc Date] <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) ) ), ShippedSales[Customer Name], "Sales Units Sum", SUM ( ShippedSales[Qty] ) ), [Sales Units Sum] )You might have to specify the sort order (ASC, DESC)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- AnonymousNot applicable
It worked, thank you!