Forum Discussion

dgelfuso's avatar
dgelfuso
Icon for Helper I rankHelper I
5 years ago

Dividing columns from two calculated tables

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): 

EOM = EOMONTH(Dates[Date],0)

 

I am now trying to divide the Monthly Bookings and the sales Monthly Shipments so that I can show book-to-bill by month.

 

Monthly Bookings =
SUMMARIZE (
AMCBookings,
Dates[EOM],
"Total", SUM (AMCBookings[Total])
)
 
Monthly Sales =
SUMMARIZE (
AMCSalesLog,
Dates[EOM],
"Total", SUM (AMCSalesLog[Sales])
)
 
I cannot seem to figure it out.  Can anyone help?
Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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])
    )
    )

    • dgelfuso's avatar
      dgelfuso
      Icon for Helper I rankHelper I
      5 years ago

       

      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 BookingsTotal Sales
      10/31/20200120,000,000.00
      10/31/202090,000,000.00 0
      • dgelfuso's avatar
        dgelfuso
        Icon for Helper I rankHelper I
        5 years ago

        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])
        )

    • dgelfuso's avatar
      dgelfuso
      Icon for Helper I rankHelper I
      5 years ago

      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 BookingsTotal Sales
      10/31/2020100,000,000.00120,000,000.00
      9/30/202090,000,000.00 110,000,000.00