How to bring table totals row from one table into another table

[Perry_White]

I'm working to replace a manual Excel report from Dynamics BC.  This report breaks down weekly invoicing by sales rep (columns) in Table 1.  Occasionally, some invoices contain reimbursements, and these line items are not revenue.  Table 2 summarizes any by month.  The first row of Table 2 are the totals from Table 1.  The remaining rows deduct reimbursements for any reps in the given month, with a final adjusted total of year-to-date sales revenue by rep.

I've built both tables independently in different files, only because I was learning and breaking the problem down.  Now that I have each table working, I need help making the totals from Table 1 the first row of Table 2 so that the actual revenue can be calculated.

Thanks in advance!

Perry

[Perry_White]

@v-eqin-msft ,

Thank you for getting back to me!  I really appreciate it.  Your post gave me an idea to run with, and I came up with the following Measure that let me derive the table I needed:

Totals =

Var InvoiceTots = SUMMARIZECOLUMNS(

Customer_Ledger_Entries,

"DR",SUM(Customer_Ledger_Entries[DR]),

"FW",SUM(Customer_Ledger_Entries[FW]),

"JS",SUM(Customer_Ledger_Entries[JS]),

"Totals",SUM(Customer_Ledger_Entries[WK Total])

)

Var CompleteTots = NATURALLEFTOUTERJOIN(InvoiceTots,

ADDCOLUMNS(InvoiceTots, "Month", "",

"Month_Sort", "A")

)

Var FinalTable = UNION(CompleteTots, ItemLedgerEntry)

return FinalTable

The ItemLedger query pulls entries from the BC table of that name, filters out all line items that are not reimbursments, and then pivots the remaining items to summarize by sales rep by week.

Thank you again for the base idea!

Regards,

Perry

[v-eqin-msft · Accepted solution]

Hi @Perry_White ,

According to my understanding, you want to calculate the Revenue=Total - Reimbursements based on each month , right? 

I try to reproduce the Table 2 but I doubt how could put a single Total to the first row since there are many Month . Sorry for my bad explanation...Please kindly take a look at my method shown below.

 1. Here is the data sample.

2. Use SUMMARIZE() to group Table 1 by month.Then use LOOKUPVALUE() to find the matched Reimbursements:

Table =
ADDCOLUMNS (
    SUMMARIZE (
        'Table 1',
        'Table 1'[Invoice Date].[MonthNo],
        "Total", SUM ( 'Table 1'[Total] )
    ),
    "Reimbursements",
        LOOKUPVALUE (
            'Table 2'[Reimbursements],
            'Table 2'[Month], 'Table 1'[Invoice Date].[MonthNo]
        )
)

3. Add Revenue column

Revenue =
[Total] - [Reimbursements]

The final output looks like this:

Please kindly check the pbix file here and let me know if I can further assist you.

Best Regards,
Eyelyn Qin

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