Forum Discussion
Helper IIIHow to design an open position?
Hello,
I have an Excel file with 2 tabs, the first tab contains list of purchase and sale contract with their respective quantities.
The second tab contains matching/marriage between one or many purchases and one or many sales.
I need to produce an open position of each contracts (whatever it is a purchase or a sale), as per below layout
| CtrType | CtrRef | Counterparty | CtrQty | Jan 2020 | Feb 2020 | Mar 2020 | .... | Balance Qty |
| P | P-0001 | Supplier 1 | 10'000 | 2'000 | 3'000 | 5'000 | ||
| P | P-0002 | Supplier 2 | 7'500 | 7'500 | ||||
| S | S-001 | Customer 1 | 20'000 | 2'000 | 3'000 | 15'000 |
Since the number of columns varies over time and execution of contracts, I was thinking to use a Matrix but the problem is that the Row Total is summing the value from Jan to the end whereas I would like to calculate the remaining Balance Qty = CtryQty - SUM(MonthlyDeliveryQty)
How can I design such layout output? As I am not an expert, any suggestion is welcomed.
As data is not sensitive I could attached the PBIX File and Excel Source file but I don't know how to do it.... 😞
Thanks.
Sylvain
Hi, Sylvain74
According to your description, you can try this calculated column:
Balance Qty = var _SUM_MonthlyDeliveryQty=[Jan-20]+[Feb-20]+[Mar-20] return [CtrQty]-_SUM_MonthlyDeliveryQtyYou can add more columns in the variable as you wish.
And you can get what you want, like this:
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- v-robertq-msft
Community Support
Hi, Sylvain74
According to your description, you can try this calculated column:
Balance Qty = var _SUM_MonthlyDeliveryQty=[Jan-20]+[Feb-20]+[Mar-20] return [CtrQty]-_SUM_MonthlyDeliveryQtyYou can add more columns in the variable as you wish.
And you can get what you want, like this:
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sylvain74Hello Robert,
Thank you very for your suggestion! In the meantime I found a solution which is really closed to yours.
Here below are the steps in Power Query (Transform Data), I went through to achieve my goal.
- Create a time table DimCalendar with a dedicated column for future headers (such as Prev. April 2020, April 2020..., Dec 2020, In 2021)
- Make an inner join with my "Matching" table to get this new DeliveryPeriod column coming from DimCalendar
- Remove all uncessary columns in order to avoid duplicated lines during the pivot step
- Pivot the column DeliveryPeriod and sum over the delivered quantity.
Thanks
- v-robertq-msft
Hi, Sylvain74
If you have solved your problem, would you like to mark my reply as a Solution, which can make more community members view and reference?
Thanks in advance!Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.