Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

ccano
8 - Asteroid

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 #ProductInvoice Line Amount Invoice Total
123Air Pods $                           249.99 $      5,125.95
123Monitor $                           699.99 $      5,125.95
123Apple Mouse $                             75.99 $      5,125.95
123MacBook Case $                             99.99 $      5,125.95
123iPhone 20X $                       3,999.99 $      5,125.95
6 REPLIES 6
BrandonB
Alteryx
Alteryx

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
Alteryx
Alteryx

Image below and workflow attached

 

invoice.png

ccano
8 - Asteroid

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
Alteryx
Alteryx

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
8 - Asteroid

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

Qiu
20 - Arcturus
20 - Arcturus

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

1223-ccano.PNG

Labels