cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors