From Sales and VC rows to CM column

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!

Common Use Cases

Accepted answers

Jonathan-Sherman

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

20200910 - SummaryTable.yxmd

All comments

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.

sample.yxmd

Antonello

Hi Jonathan,

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

Thank you

Input and desired outcome.png

Pivot example.xlsx

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

Pivot example.xlsx

Jonathan-Sherman

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

20200910 - SummaryTable.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703