Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Solved! Go to 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
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
Thanks a lot for the answer, very useful!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |