Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a Dates table, a bookings table, and a sales table.
I created two tables which summarize total bookings and total sales by EOM (End of Month from the Dates table):
I am now trying to divide the Monthly Bookings and the sales Monthly Shipments so that I can show book-to-bill by month.
Hi,
Please share some data and show the expected result.
I would like the data to look like below and then be able to divide Total Bookings by Total Sales for each EOM.
EOM | Total Bookings | Total Sales |
10/31/2020 | 100,000,000.00 | 120,000,000.00 |
9/30/2020 | 90,000,000.00 | 110,000,000.00 |
@dgelfuso , You can join then with date table and then analyze together with dates table
And try a measure like
divide(sum('Monthly Bookings'[Total]), sum('Monthly Sales'[Total]))
Or you can combone these two table and analyze
new Table
union (
SUMMARIZE (
AMCBookings,
Dates[EOM],
"Total Bookings", SUM (AMCBookings[Total]),
"Total Sales", 0
)
,
SUMMARIZE (
AMCSalesLog,
Dates[EOM],
"Total Bookings", 0,
"Total Sales", SUM (AMCSalesLog[Sales])
)
)
This is the result. I think I need one row per EOM date so that I can perform math on the two columns.
EOM | Total Bookings | Total Sales |
10/31/2020 | 0 | 120,000,000.00 |
10/31/2020 | 90,000,000.00 | 0 |
I had to create another table that resulted in one row per EOM date (see below). I would think it would be possible to do it in one step instead of two.
Monthly Table Final =
SUMMARIZE (
FILTER('Monthly Table','Monthly Table'[EOM]<>blank()),
'Monthly Table'[EOM],
"Total Bookings", SUM ('Monthly Table'[Total Bookings]),
"Total Sales", SUM ('Monthly Table'[Total Sales])
)
Hi @dgelfuso
Could you tell me if your problem has been solved? If it is, share your workaround and kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |