Pivot Table with 2 Sheets...

[EFA10]

Hi,

I have created a Pivot table with 2 sheet:
1 sheet is Invoice with all information (date, Group, customer, lines, total, category...)
2 sheet is target Budget allocated to the customer in category per year.

I would like to create a report with :

in row : Group, name of customer,
in colunms : Year (in my Invoice data 3 years are presents, 2018/2019/2020 under Period) per period - 3 columns with the total invoice and the budget

My Pivot works well for all information coming from invoices but not the budget.
How can I do ?
Many Thanks.

Eric

[alansidman]

Have you considered bringing both sheets/tables into Power Pivot and joining the two "tables" on a common field. Then you can create a Pivot Table employing both tables. If you upload sample data to this site (See yellow banner at the top of this page describing how to do that), I am sure that someone here will assist.

[EFA10]

Hi Alan,

Thank you for your reply.
I have set in attachment a example of what I would like to do!
After having the budget, we can compare the delta between the actual turnover and the Yearly budget (difference, amount and %, and gap to be realized)
The Yearly budget in False in my Pivot table.
I have tried a lot of configuration between the two table, but something I miss!
I can't connect correctly.

Thanks for your help.

Take care of you and your family in this bad situation.
Eric

[alansidman]

Look at the attached and determine if this is what you are looking for.

[EFA10]

Hi Alan,

Look good :-)
In attachment the result towards I a looking...
Can you explain because I can't see relation between table, but no fields and the table above the Pivot.
The DATA Invoices actually is 110.000 lines and will be bigger at the end of the Year.
One thing is important is to have a filter option per period (January, february, march, ...) to select the comparaison between a period.
After having the explanation how to do, I am sure I can be analyse in deeper.

Thanks for your feedback and explanation.

Eric

[oeldere]

Sheet Invoices

In case you add the text manualy in Column A and B.

You can use this formula for that.

See the attached file.

I2 =MONTH(C2)
J2 = YEAR(C2)
K2 = DAY(C2)

With this data it is a lot easier to get the data in order of month in a pivot table.

[EFA10]

Hi,

Nobody have a idea to help me?
Thanks for your timer and help.
Kind regards,

Eric

[alansidman]

Here is a picture of what the merger of the two tables looked like in Power Query and the Mcode.

Please Login or Register  to view this content.

[EFA10]

Thank You Alan.
I have done this before with my data and the results are not correct!
I don't understand, the relation and the merge look ok.
It is a pitty I can not reproduce it!

[alansidman]

How does your data differ from the data shown in your sample? Are the layouts exactly the same?

[EFA10]

Not the same :
Budget :Year / Clients_Group / Partner_id / Partner Name / PRODUCT CATEGORY 2 / Budget
Data :Year / Period / Date_Invoice / Invoice/Number / invoice_line_ids/activity_type_id/name / Clients_Group / Partner_id / Activity Name / Partner Name / Quantity / invoice_line_ids/price_subtotal / subtotal / Invoice line/Description / Invoice line/Product/Reference / Product_Category_2 / Internal Category / invoice_line_ids/account_id/display_name / amount_total_company_signed / amount_total_signed /Rate / invoice_line_ids/currency_id/currency_unit_label / invoice_line_ids/currency_id/symbol / Sales / RM / Backup / New18_19

[alansidman]

Will the merge by on the same fields. Partner ID and Year. Here is a suggestion: Create your merge with the minimum number of fields from each sheet. And so long as the merger creates the expected results, then add an additional field until something goes astray and you can then determine why it is not working.

[VKS]

Hello Eric,

Please check and advise if this is what you want.

Best Regards/VKS

[EFA10]

Hello,

My Power Query is perefct with only one problem.
Example in attachment.
Budget per customer with actual figures > Correct but if one customer not yet invoiced, the budget doesn't appeared!
So my Delta is not correct between the actual figures and the reachable (total budget).
You can simulate in my example, you change the invoice for customer3 to customer1 and you will see the problem.

Any idea?
Thanks.
Good day.

Eric