Greetings all! Total Power BI noob here, looking for help with incorrect measure totals. I've read quite a few posts/articles on the subject, but can't seem to overcome the problem. I'm going to try to explain my data as best I can, but unfortunately, I don't think I can upload any samples at the moment. I work with protected health info, so I'd need to completely sanitize the data before uploading. Let's see if we can get somewhere without it. If not, I may try to produce an anonymous dataset that can be uploaded.
Some background into the data and the goal. My company provides advances to medical providers on bills where they are awaiting payment from a patient's court settlement. The core fields in our data are Bill ID, Provider Name, Patient Name, Funded Date, Advance Amount and Total Collected. This data is exported to Excel and loaded into PBI and we'll call this table Dataset 1. When a provider pays back our advance, there is a Fee % applied against the amount advanced, and that rate depends upon how many days have passed since the Funded Date. I am attempting to build a report that will allow the provider to choose a payoff date and patient(s) and it will then generate a payoff report for them.
In addition to Dataset 1, I have another table named Date Table and another named Rate Lookups. The Rate Lookups table is a simple one with number of days in the first column and the correponding Fee % in the second column. The Date Table contains a series of dates in the first column, to be used in a slicer for the user to select the Payoff Date. I added a second column that contains a formula to count the numbers of days between the corresponding date in the first column and today's date. I then used those numbers to filter the slicer so that it would only include tomorrow's date and the next 60 days. Currently, neither of the three tables have any defined relationships with one another.
In my report, I've created a table and a matrix to display the information. The table provides a detailed summary for each bill that is selected by the filters. The matrix breaks out the payoff amounts by patient and provider, as this Provider has separate entities (Provider Names) and makes their payments to us via checks drawn against their separate entities.
I have added slicers that allow the user to choose Payoff Date, Patient Name, Provider Name and Funded Date. For the calculated fields, I created the following measures:
Days from Funding = datediff(min('Dataset 1'[Funded Date]),[Selected Value],day) <---Note, the selected value comes from the Payoff Date slicer
Fee % = value(lookupvalue('Rate Lookups'[Rate],'Rate Lookups'[Day],[Days from Funding]))
Fee Amt = round(sum('Dataset 1'[Advance Amt]) * [Fee %],2)
Payoff Before Collections = sum('Dataset 1'[Advance Amt]) + [Fee Amt] <---Note, this field is not displayed in the table. I only used it to account for potential prior collections on a bill. I imagine this is probably an extra step, and I could probably write this into the following formula, but I'm not there yet.
Payoff Amount = [Payoff Before Collections] - counta('Dataset 1'[Total Collected])
All of this is working correctly, except for the totals in my table and matrix. Each individual row calculates correctly. My problem starts with the Days from Funding column. The total seems to be reflecting the largest number in the displayed column. From there, the same thing happens with the Fee %, and ultimately the total Fee Amt and total Payoff Amount. When I review the figures in the totals only, mathematically it makes sense. When I multiply the total Advance Amount by the 'total' Fee %, that figure matches the total in the Fee Amt total. And the total Payoff Amount is equal to the total Advance Amt plus the total Fee Amt. But because each individual bill does not necessarily share the same Days from Funding and Fee % that is shown in the totals, the totals are inaccurate.
Again, I have spent a fair amount of time researching this issue, and I see that it is a commmon one. Unfortunately, I have not had that a-ha moment. I did find a HASONEVALUE formula that was helpful in not displaying a measure total (useful for the Days from Funding and Fee % measures, but all totals were still off). I should reiterate that I am completely new to this. My only prior experience with Power BI was simply uploading updated spreadsheets into someone else's report. And some of the syntax in the above measures may be the result of me trying to overcome other issues. For example, in the Fee % measure, I added the 'Value' portion after having difficulty getting the Fee % to show as a percentage and not a decimal. And in the Payoff Amount measure, I don't recall adding the 'Counta' piece...I think that may have come about by entering a quick measure? So with that said, I realize that my practices may be sloppy, and I appreciate any help in that regards. But I espeically look forward to any feedback on correcting my measure totals. Thanks in advance for your help!
to our YouTube channel 
