Forum Discussion

Khpuryear's avatar
Khpuryear
Helper I
1 year ago
Solved

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 🍻

 

  • Anonymous's avatar
    Anonymous
    1 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

  • 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.

  • Anonymous's avatar
    Anonymous
    Not 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.