Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Sylvain74
Helper III
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

CtrTypeCtrRefCounterpartyCtrQtyJan 2020Feb 2020Mar 2020....Balance Qty
PP-0001Supplier 110'0002'000 

3'000

 5'000
PP-0002Supplier 27'500    7'500
SS-001Customer 120'0002'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
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1605070319119.png

 

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.

View solution in original post

3 REPLIES 3
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1605070319119.png

 

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.

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

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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