Getting the amount on a leveled data

[Albert2107]

Hi I need help I am struggling to do this on excel and I don't know how to start this on alteryx either.

I have a file here, and as you can see there's a spend data with categories which are indented for each level and each level has a corresponding amount and subtotals. I want to get the amount of each spend by level without double counting. I'm not sure if you get what I'm trying to do so I'll just show an example image below. I am trying to do this in pivot but failed because the total amount always bigger (because of duplicated amounts).

[IraWatt]

Hey @Albert2107, I'm having a look at this, would you be able to give a simpler example which we could try scale up? 

Thanks,

Ira

[Albert2107]

Hi @IraWatt , thanks in advance. I'll drop here a sample of the look of what I'm trying to achieve.

As you can see in the P&L tab (raw data) the data are arranged like an income statement (vertical), categories are leveled and indented, now I want to achieve the arrangement in output tab by getting the corresponding amount by levels of categories (horizontal) and without double counting of the amounts. I'm not sure if I explain it well.

[ddiesel · Accepted solution]

Hi @Albert2107!

@IraWatt might jump in with a solution (he's the expert around here!), but I think I understand what you are trying to do.

The "P&L (new)" sheet utilizes the outline feature, which is a type of formatting in Excel. Unfortunately Alteryx can only read in cell contents, irrespective of any formatting.

To put the contents of "P&L (new)" into levels so you can create the desired format in Alteryx, you will need to find a way to identify the indents. I am not aware of any way to do this in Alteryx, but I found an excel-based work around by @BenMoss in another community post:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Column-Data-Indenting-and-Organization...

Check this out and let us know if it helps!

Thanks,
Deb

[IraWatt]

@Albert2107 I tried on your example but It got a bit out of hand. I would definitely check out @ddiesel's solution !  

[Albert2107]

Thank you both, I'll check @ddiesel's reply and accept it as a solution for the meantime. 

[ddiesel]

Thanks @Albert2107!

Please let us know if it works for you.