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 |
Solved! Go to Solution.
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.
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 |
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
That's exactly what I did. Thanks for the help 🙂 😊