Generating a column with a fixed amount based off of Invoice total

[ccano]

Use Case: 

I'm trying to replicate a calculation I use in Tableau for Invoices with multiple lines. I need Alteryx to only reflect the grand total amount based on a column  and not based off the invoice line amount. 

So if invoice #123 grand total is $5,125.95 and I have different 5 products (at different prices), I'd like to reflect only the grand total amount  , rather than the summation  ($5,125.95 ) five times and inflating the invoice amount to $25,629.75. 

Here's the calculated (formula) field I'm using in Tableau to accomplish this:  

{FIXED [Inv #]:MIN([Invoice_Total])}

Invoice #	Product	Invoice Line Amount	Invoice Total
123	Air Pods	$                           249.99	$      5,125.95
123	Monitor	$                           699.99	$      5,125.95
123	Apple Mouse	$                             75.99	$      5,125.95
123	MacBook Case	$                             99.99	$      5,125.95
123	iPhone 20X	$                       3,999.99	$      5,125.95

[BrandonB]

You should be able to use a summarize tool where you group by Invoice # and sum Invoice Line Amount. Then you can use a join tool to join the results of the summarize back to the data right before the summarize and choosing Invoice # as the field that you join on.

[BrandonB · Accepted solution]

Image below and workflow attached

 

[ccano]

Thanks for your swift reply.  But I need to pivot this one invoice with other invoices and the grand total should only reflect once so that  get a distinct value at the invoice level.  I figured I'd need a special formula for that (like I use it in tableau). 

Invoice #	Invoice Total
123	$  5,125.95
456	$  1,600.99
789	$     599.99
321	$        99.99
654	$        75.99
987	$        19.99

[BrandonB]

You can also do a min in the summarize tool like you are doing in Tableau if you want to replicate the table in your last comment

[ccano]

That's exactly what I did. Thanks for the help 🙂 😊

[Qiu]

@ccano 
Same approach with @BrandonB, only with some more data and sampel flow.