First, hats off to you for wanting to work this out yourself and just getting a hint from the forum - great way to maximize your own learning.
My recommended path would be to switch from TOPN to a RANKX-based approach, and nest your RANKX measure within an IF statement that returns the measure value if <= 3 and returns BLANK() otherwise.
I hope this is enough to nudge you on track to your solution. Give a shout if you need any further assistance.
Hi @Matty. I don’t have time right now to dive into your specific problem, but, if the data volume is not too high, have often used RANKX to calculate a rank, then filtered the results to keep only those below a specified rank (in your case <= 3). Perhaps that will help.
Greg
Hi @Matty, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @Matty, we’ve noticed that no response has been received from you since the 23rd of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
The result is correct but it is not what I am trying to get. Like mentioned before I am trying to calculate last 3 sales but I would like to have it with the interaction of the date slicer.
Here’s the way I would tackle this in a general sense:
create a disconnected date table slicer
harvest the values of that
build a RANKX-based measure with a variable that returns a one column table containing top three dates, subject to the filter condition set by the harvested values from 2). The result of that measure will check each selected date value in the visual to see whether that value is in the one column table variable, and the measure will return a 1 if it is, and a 0 if not.
throw the measure created in 3) into the filter pane for the relevant visuals (either at the individual visual level, or at the page level) and set it equal to 1.
I hope this is helpful, and puts you on the right track to a solution.
Brian
P.S. While not a RANKX-based problem, this solution that I provided yesterday uses all the other aspects of the strategy I laid out above.
Hi @Matty did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
The issue that I have I can’t make it dynamically using date slicer:
If I filter the date slicer to 13/01/2021 then TOP3 will not provide me the result that I want to see, that’s why I tried to use the RankX formula but unfortunately, I am not that good as you guys. Brian did give me hints on how I should approach this topic but I am failing it
Thank you @AntrikshSharma for your help, honestly appreciated it.
I studied your formula to have a better understanding, I added few comments, do you mind to check it?
What is the reason to add Keepfilters? This is the first time I think where I’ve seen VAR functions used inside of formula pattern.
Usually, I used and seen on yt VAR 1, VAR 2 etc Return Calculate,VAR1,VAR2 etc. whereas your pattern is completely different level. Thank you for showing me this!
3 Latest Sales by Revenue 2 v2 =
CALCULATE (
[P&L Revenue],
//Calculate revenue
//why do we use keepfilters KEEPFILTERS (
CALCULATETABLE (
VAR Temp =
ADDCOLUMNS (
SUMMARIZE ( Data, ‘Job category’[Financial level 1], Data[Completed] ),
“@PL Revenue”, [P&L Revenue]
) // Creates a table for each job category and date completed, adding extra column with revenue for that date and job
VAR TempWithoutBlank =
FILTER ( Temp, [@PL Revenue] <> 0 )
// filtering above table to remove revenue that doesnt equal to 0
VAR TopPL =
TOPN ( 3, TempWithoutBlank, [@PL Revenue], DESC )
// calcuate top 3 from above table
RETURN
TopPL,
REMOVEFILTERS ( ‘Job category’[Financial level 1] )
//remove filter on job category? so we can filter the data by job category or financial level?
)
)
)
The formula pattern nearly works meaning I can apply the date slicer filter but the results don’t give me TOP3 dates.
@Matty CALCULATETABLE returns a list of Financeal Level 1, when these values are applied to the filter context they will overwrite the existing filter and you will only get the Total P & L of all those values, so to not overwrite the existing filter and to create an intersection between filter context outside CALCULATE and inside CALCULATE we need to use KEEPFILTERS.
REMOVEFILTERS is used so that at each cell of the visual all the Financial Level are visible otherwise TOPN will only receive 1 row containing the value visible in the current cell of the report.
Regarding the above requirement, I see you are using different columns so the granularity is also different, try to adjust the formula accordingly.
