Days Sales Outstanding (DSO) per company - matrix

[cfcastell]

Hi,

I'm trying to build a matrix that shows the DSO per group of customers in the rows and per company in the columns, using three different files.

• Sales: The first file shows sales and contains three columns, showing the company, client number and the amount of the sale.
• Debt: The scond file shows the debt and contains as well three columns, showing the company, the client number and the amount of debt.
• Classification: And the third excel file has two columns, one with the client number and the other with the customer group.

In the first two files, the customer number might be repeated with different amounts of sales/debt.

I firstly created a relationship between the client column of the sales file and the client column of the classification file; and a second relationship between the client column of the debt file and the client column of the classification file.

Then I created a new measure, with the following computation:

DSO =
180/(DIVIDE(
 SUM('Sales'[Amount]);
 SUM('Debt'[Amount]))
)

I inserted a matrix with the customer group in the rows, and the DSO measure in the values. The computed numbers make sense. However, when I try to compute the DSO per company by dropping company in the columns option, the numbers do not make sense anymore. Apart from that, numbers differ depending if I take the Company column from the Sales file or the Debt file.

I don't really know how to proceed with it. I would really appreciate if anyone could help me.

Thanks in advance

[v-ljerr-msft · Accepted solution]

Hi @cfcastell,

In your scenario, I would suggest you merge Sales table and Debt table into a single table in Query Editor first, and create a relationship between the new merge table and the Classification table with the client column, and then try the formula below to see if it works in your scenario.

DSO =
DIVIDE (
    180,
    DIVIDE ( SUM ( 'Merge1'[Amount] ), SUM ( 'Merge1'[Debt.Amount] ) )
)

For more details about how use merge queries option in Query Editor, you can refer to this article. 

Regards

[cfcastell]

Thanks a lot for the answer, very useful!