Output Unique Combinations with SumIFs

[taxguy33]

I am new to Alteryx, so struggling to find a solution elsewhere on the message board.  I have two columns "Category" and "Ledger Name" with anywhere from 5 - 15 distinct values in them. 

Example below:

Category            Ledger Name

PPE                     US Ledger

PPE                      US Ledger

PPE                      Canada Ledger

Prepaid                China Ledger

Prepaid                Germany Ledger

Goodwill                 US Ledger

I want an output that finds all the unique values in both columns and then creates two columns in the following format below:

Category            Ledger Name                  Closing Balance

PPE                    US Ledger                         A sumifs formula based on both Category / Ledger name on another tab output

PPE                     Canada Ledger                A sumifs formula based on both Category / Ledger name on another tab output

PPE                    China Ledger                    A sumifs formula based on both Category / Ledger name on another tab output

PPE                    Germany Ledger              A sumifs formula based on both Category / Ledger name on another tab output

Prepaid              US Ledger                         A sumifs formula based on both Category / Ledger name on another tab output

Prepaid              Canada Ledger                 A sumifs formula based on both Category / Ledger name on another tab output

Prepaid              China Ledger                    A sumifs formula based on both Category / Ledger name on another tab output

Prepaid              Germany Ledger               A sumifs formula based on both Category / Ledger name on another tab output

I have tried cross-tab and the unique tools, but was not able to determine which was correct or what combination will get me here.

[Thableaus]

Hi @taxguy33 

Do you have a sample of your data from your input and desired output just to make it more clear?

Cheers,

[danilang · Accepted solution]

Hi @taxguy33 

Here's a possible solution

It starts by finding the unique categories and ledgers and performing a cross join using the Append Fields tool.  This is joined back to the original data on Category and ledger name.  The joined records are summarized and the unjoined records gets a 0 Closing Balance before being unioned and sorted

This input data

gives this output

Dan