As Show in screenshot, we aim to do this. We want to add a calculated field of percentage drop based on count of visits. Then we need to create a chart like in the screenshot. The line in combo chart depicts percentage drop and it should start from 100%.
Please suggest how we can add a calculated field for percentage drop.
Hello @Abhishek_24, in order to accomplish this, you will want to utilize the lag function. Basically, you will want to get the Count of Visit value for the previous Rank of Visit that you are currently on.
lastCount = lag(sum({Count of Visit}), [{Rank of Visit} ASC], 1)
Utilize a table visual to test these values and make sure they are returning correctly.
Then you will want to take 1 minus the percent of the 2 to receive the value you want. If you Count of Visit field is not an aggregate value, you will need to aggregate it in order to avoid an error. It would look something like this:
Percentage Drop = 1 - ({lastCount}/sum({Count of Visit}))
If Count of Visit has been aggregated already, then removing the sum calculation should be fine. I will mark this as the solution, but if you have any questions when implementing this, please let me know!
Thank you @DylanM it worked.
