Forum Discussion
Top n grouping value by date
Hi,
I haven't found this case yet I'm trying to solve on the forum. I'd like to show a table with the top N with data responding to the slicers. The data is at the month granularity, but I would like to be able to show the top N where the date could be grouped into a quarter or year.
For example if this is the data:
| Project | | Date | | Value |
| Project 1 | | 1-Jan-21 | | 1000 |
| Project 1 | | 1-Feb-21 | | 1200 |
| Project 1 | | 1-Mar-21 | | 1000 |
| Project 1 | | 1-Apr-21 | | 1500 |
| Project 1 | | 1-May-21 | | 1900 |
| Project 1 | | 1-Jun-21 | | 2100 |
| Project 2 | | 1-Jan-21 | | 500 |
| Project 2 | | 1-Feb-21 | | 500 |
| Project 2 | | 1-Mar-21 | | 500 |
| Project 2 | | 1-Apr-21 | | 2200 |
| Project 2 | | 1-May-21 | | 500 |
| Project 2 | | 1-Jun-21 | | 500 |
| Project 3 | | 1-Jan-21 | | 3000 |
| Project 3 | | 1-Feb-21 | | 3000 |
| Project 3 | | 1-Mar-21 | | 3000 |
| Project 3 | | 1-Apr-21 | | 1000 |
| Project 3 | | 1-May-21 | | 1000 |
| Project 3 | | 1-Jun-21 | | 1000 |
Currently, if I filter on "Q2 2021", the top 1 project would show as Project 2, with a value of 2200. How can I show a table where the top 1 in this case would show Project 1, with a value of 5500, thus aggregating the values for the date selected? It should be dynamic to be able to aggregate in cases where a month, quarter, or year is selected in the slicer.
Alternatively, how can I rank these projects?
Thanks in advance
4 Replies
VJR , Ty meausres like
measure = sum(Table[Value])
Top project =
CALCULATE(max(Table[Project]),TOPN(1,allselected('Table'[Project ]),[Measure],DESC),VALUES('Table'[Project ]))
Hi amitchandak,
If I understand correctly, this measure will always return the top 1 because of the max function in the expression of the Calculate.
In reality there are hundreds of projects. How could I achieve a top 10?
Hi Anonymous ,
Thanks for your reply! This is what I initially tried, but it only returns the top 1. I think may be because of the max function in the expression of the Calculate.
