Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BlindSquirrel
Frequent Visitor

Another post about inaccurate measure totals

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!

 

 

 

1 ACCEPTED SOLUTION
BlindSquirrel
Frequent Visitor

I was able to resolve this issue by changing the formula on my Fee Amt calculation.  The revised formula is:

 

Fee Amt = sumx('Dataset1',Dataset1[Advance Amt]*[Fee %])
 

View solution in original post

6 REPLIES 6
BlindSquirrel
Frequent Visitor

I was able to resolve this issue by changing the formula on my Fee Amt calculation.  The revised formula is:

 

Fee Amt = sumx('Dataset1',Dataset1[Advance Amt]*[Fee %])
 
BlindSquirrel
Frequent Visitor

Bumping for visibility.  I have updated the post to include a sample file for review. The file has since been removed Appreciate any feedback, even if just to let me know if you can access the sample file.  Thanks!

v-shex-msft
Community Support
Community Support

Hi @BlindSquirrel,

Any update on this? Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin, I had edited my original post to include a link to download a pbix.  Let me know if you are able to access the sample file, and can assist.  Thanks  I have solved the issue and have removed the sample file

 

@v-shex-msft 

parry2k
Super User
Super User

@BlindSquirrel amazing details you have provided, it will be super easy if you can share the pbix file, remove sensitive information before sharing, and that will help to get the solution.

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks @parry2k, I will try to work to create a sanitized version that I can upload, but not sure when I'll be able to get the time.  

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors