Forum Discussion
Help with Matrix Visual for Invoices and Collections Activities
Hello 👋 I'm having trouble with a Matrix visual. Details below...
Use Case
I have invoices with underlying collection activities. I need a row with the InvoiceID, Customer, and the earliest activity, notes, etc., and then I need to expand to show historical collections activities (Based on Event ID) as they will have unique dates, notes, etc.
Problem
I think I was able to get this started with a matrix, but I need to display the original invoice total and current invoice balance, without it summing up the values in top row. The total and balance does need to be displayed at the bottom as there will be numerous invoices.
Below is an example of one invoice, with two activities.
Here is view when expanded:
Here is the view when rolled up:
The problem is the Total is 2000.00 and the Balance is 1800.00. What I need is the Total to be 1000.00 and the Balance to be 900.00. Something like this...
I'm assuming it's summing by activiy. My initial thought was to change the format of the fields to "Don't Summarize," but that did not work.
Here is the table and field names. The main fields in question are Invoice ID, DebitAmt (this is the total), and NewBal (this is the balance).
Is what I'm aiming for possible?
Thanks 🍻
- Anonymous1 year ago
Hi, Khpuryear
You can try the following methods.
Measure Total = IF(HASONEVALUE('Table'[Collection Events]),SUM('Table'[Total]),AVERAGEX('Table',[Total]))Measure Balance = IF(HASONEVALUE('Table'[Collection Events]),SUM('Table'[Balance]),AVERAGEX('Table',[Balance]))Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- Ashish_MathurSuper User
Hi,
Cannot understand your question from just images. Share some data, explain the question and show the expected result. Share data in a format that can be pasted in an MS Excel file or share the download link of the file.
- AnonymousNot applicable
Hi, Khpuryear
You can try the following methods.
Measure Total = IF(HASONEVALUE('Table'[Collection Events]),SUM('Table'[Total]),AVERAGEX('Table',[Total]))Measure Balance = IF(HASONEVALUE('Table'[Collection Events]),SUM('Table'[Balance]),AVERAGEX('Table',[Balance]))Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- KhpuryearHelper I
This can be closed out as it has been resolved.