From Sales and VC rows to CM column

[Antonello]

Hello Community,

I would have a pretty simple question but potentially beneficial for many users.

I have a transaction register where each row represents either a Sales External or Variable cost account for certain products.

I want to aggregate data by Product and Region and have 2 columns: Sales External and Contribution Margin (calculated as Sales External + VC)

I tried with Cross-tab tool but I'm definitely missing something....

Thanks for the support!

[Jonathan-Sherman]

Hi @Antonello,

Could you provide some sample data and the output your looking to achieve? It might help us get to the solution a little quicker.

Kind regards,

Jonathan

[AbhilashR]

Hi @Antonello, attached is an attempt to compute your output.

Let us know if this isn't what you are looking for.

[Antonello]

Hi Jonathan,

Sure, sorry if I didn't upload the excel dataset earlier.

Thank you

[Antonello]

Hello AbhilashR,

thank you for your proposed solution. However when a record has only VC and not Sales the output is missing that product. 

For example, I used the attached DB where Product DD has only costs (75). After running the WF I correctly see all products except for Product DD.

Thank you

 

[Jonathan-Sherman · Accepted solution]

Hi @Antonello,

In order to get the output you're looking for you just need to scaffold the data from the initial input to ensure you keep all Product & Region scenarios:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Antonello]

Thank you very much Jonathan!

I just changed the columns headers into Sales Ext and CM but apart from that it works perfectly.

Thanks also to @AbhilashR for the support.