Getting the amount on a leveled data

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).

Spend data.xlsx

Datasets

Developer

Macros

Accepted answers

ddiesel

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-Structure/td-p/157721

Check this out and let us know if it helps!

Thanks,
Deb

All comments

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.

raw data and output.xlsx

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703