cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## How 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

1 ACCEPTED SOLUTION
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_MonthlyDeliveryQty``````

You can add more columns in the variable as you wish.

And you can get what you want, like this:

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

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 3
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_MonthlyDeliveryQty``````

You can add more columns in the variable as you wish.

And you can get what you want, like this:

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

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.

Helper III

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

1. Create a time table DimCalendar with a dedicated column for future headers (such as Prev. April 2020, April 2020..., Dec 2020, In 2021)
2. Make an inner join with my "Matching" table to get this new DeliveryPeriod column coming from DimCalendar
3. Remove all uncessary columns in order to avoid duplicated lines during the pivot step
4. Pivot the column DeliveryPeriod and sum over the delivered quantity.

Thanks

Community Support

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?

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors