I am just working on a weekly accounts receivables report. My dataset contains information about customers from different regions and entities and AR balances for those customers as of every Monday.
In my report I put a summary table which allows for quick scanning - it shows Top 5 debtors + OTHER (for a given Monday I rank all customers by total overdue amount and if the ranking <= 5 then customer name is displayed, otherwise I put everything into "OTHER" category - please see first picture).
Users are able to choose a specific date for which they want to see the debtors ranking and total overdue balances. I have been also asked to add a column with a change in total overde (this vs previous week). I have created a measure to show the delta but unfortunately when I put it into my table, more than 5 debrors for every region appear (I get top 5 debtors for this week and those who ARE NOT in top 5 this week but were on the top positions last week).
I tried to make a workaround in a measure to not display those names (please see second picture) but it does not work - the total change for a given region is not the sum of the top 5 debtors + other.
Do you have any ideas how to solve this?
Thanks in advance for your help!