Forum Discussion
Helper ITop N or Rank Dynamic By Date
Hello, I am struggling to create a summation of values by top 5 states that is dynamic by date.
On the left in my screenshot is a table of data, while the red section on the right is a summary that I'd like to have. If you look at the data in my example file (link at the bottom), you will see that:
The top 5 states by value in 2018 were NH, NM, NC, WY, and TX.
The top 5 states by value in 2019 were CT, NM, CA, MO, and KS.
The top 5 states by value in 2020 were OK, IA, LA, NH, and VA.
rlussky I'm seeing different top states than you listed above in your data, but how about something like this:
TOP 5 States = VAR _TopStates = TOPN(5, 'Table',[Balance Measure]) VAR _Result = CALCULATE([Balance Measure],_TopStates) RETURN _Result
2 Replies
rlussky I'm seeing different top states than you listed above in your data, but how about something like this:
TOP 5 States = VAR _TopStates = TOPN(5, 'Table',[Balance Measure]) VAR _Result = CALCULATE([Balance Measure],_TopStates) RETURN _Result
rlusskyWow, that was much more simple than I thought it would be.
Thanks Anonymous !