Hi,
I have a table with Hours spent per components per months, like the image under and I would like to the top 10 components per months, but using the Top - N Rows function give me the top 10 of all months. (10 rows total instead of 10 rows per months)
Is there a way to have the top 10 per month?
Thanks,
Marilou
Hi @Marilou
When there are two dimensions selected in the report rows, the standard TopN function takes into account all report rows; the same goes when you order rows.
You would need to create two measures (in Measures) to overcome this behaviour:
For the first, Component rank measure, you may want to use the function Rank() to rank components by Hours spent value. It will assign number 1 for the component with the most logged hours within the time period, 2 for the second-highest, etc. :
NonZero(
-- get rank in the descresing order of components
Rank(
[Project].CurrentHierarchyMember,
Order(
-- order components from the same level with existing value
Filter([Project].[Component].Members,
[Measures].[Hours spent]>0),
[Measures].[Hours spent],
DESC)
)
)
Add the measure in columns and set a row filter <= 10 to get the top 10 rows by Hours spent within each time period. You may remove the column, the filter by rank will remain.
Now, to order those Top N elements within the time period as well, create another measure “Component rank for order” where you join the time period start date value with the rank in one value to ensure order within the time period.
An arithmetical solution would be getting a sum from the timestamp of the time period start date and the rank value:
DateToTimestamp(
[Time].CurrentHierarchyMember.StartDate)
+
[Measures].[Component rank]
NB! Use your name for the “Component rank” measure!
Add this measure in columns and order by it. Remove the column afterward.
See how it looks (before the columns are removed):
Best,
Ilze , support@eazybi.com
