Matrix Setup

[Husseinch01]

Hello, i want to create a matrix with the below format, here is what i have to work with: In my table called BLE, i have : Amount, Type. I have a date table, and a table that has customer names, both connected to my BLE table. The cash in, cash out, opening and closing are all calculated measures with the "Amount" based on the "Type". For actual, the type is blank, and for expected are the Types : VLE, CLE, SO, PO. so i have actual opening, actual cash in, actual cash out, actual closing and expected opening, expected cash in, expeced cash out and expected closing.How can i create the below matrix with my available data? If you need any more info please let me know, and thank you in advance!

[Anonymous · Accepted solution]

Hi @Husseinch01 ,

Thanks for reaching out to our community. Is this what you want? As you can see, names are under 'Cash in' or 'Opening'.

In the row field, put the type field above the name. And then use the drill down to drill on rows.

The attachment is for your reference.

Using drill-down actions with the matrix visual

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataNinja777]

Hi @Husseinch01,

Based on your description above, I understand that you have customer names, but while Sales Orders (SO) are associated with transactions involving customers, Purchase Orders (PO) are generated from transactions with vendors, not customers. If you want to capture both cash inflows from customers and cash outflows to vendors comprehensively, the best approach is to use your bookkeeping data from your ERP system.

Typically, most transactions related to cash inflow and outflow are recorded through the Accounts Receivable (AR) module for sales transactions and the Accounts Payable (AP) module for purchase transactions. What you need to do is identify the journal entries in the company's accounting system that include bank accounts. Then, use the offsetting account of these bank transaction entries to determine either cash collections from customers recorded with customer codes through the AR module, or cash payments to suppliers, recorded with supplier codes through the AP module. In some rare cases where transactions do not go through the AP or AR modules, GL accounts like tax and interest GL accounts may be used as the offsetting side of the bank journal entries.

The article in the link below explains how to prepare a cash flow analysis if you have access to the company's bookkeeping records maintained in your company's ERP system:

https://p3adaptive.com/power-pivot-cracks-direct-method-cashflow-conundrum/

However, if you don't have access to journal entry data, an alternative solution will need to be developed. In my experience, for a standalone company with a complete set of bookkeeping journal entries that involve direct AP and AR transactions with third-party customers and suppliers (without intermediary intercompany transactions), using the offsetting account information from bank journal entries is one of the most effective methods for compiling a cash flow analysis. This approach accurately identifies each cash flow transaction in the company, specifying where the money came from and where it was paid.

Best regards,

[Husseinch01]

here's what i did, i only still need a way to put my customer names underneath the opening, cash in, cash out and closing, which i still haven't figured out how to:

i made a custom column that displays "actual" and expected based on my requirements, i used that column, with the "cashflow" dimension that has cash in, cash out, opening and closing, and it worked, i also checked the numbers and it's working perfectly fine. now the idea is that i want my "name" metric to show under each value of the cashflow which is what i am trying to achieve. btw i turned on the "switch values to rows" in my matrix under "Values" option to make the cashflow dimensions as rows.