Prevent many to many connection after unpivot

[stefanhamann]

Dear all, 

I'm trying to find a way through but I'm quiet stuck right now. 

I have a table with budget per month.

• Each row has an unique buget ID f.e. AS_20_001
• and 12 columns, one for each month by date f.e. 01.07.2020 (always the first of the month) 
• the date columns provide the budget for the month

To get the data structured I used the unpivot function for the month columns, so I can now calculate numbers on my budget

The second table is the requisition table which provides the order in the fysical year. 

• there can be multiple orders to the same budget ID
• also there can be orders without a budget ID 

I want to calculate sum(Budget[Budget] - sum(Requisition[]budget]) - by the budget ID to get the overview about when was my budget used and how many is left to date. 

Problem is that with the unpivot function the budget ID is not unique anymore and the connection is many-to-many. I tried to put a bridge table in between but it does not work either (for my knowledge) 

Can someone give me a hint? Is there a function available which i missed? All help is really appreciated.  

Thank you and have a wonderfull weekend. 

[VijayP]

Can you  share the pbix to see the structure of the data

[stefanhamann]

Thank you for the opportunity. Additionally I want to say the budget file is a handmade structure which could be changed. The other sources are reports which cannot be changed. 

https://www.dropbox.com/s/yrii72uz8fxk4i9/Forum-Reducing-Data.pbix?dl=0 

[stefanhamann · Accepted solution]

After a while of additional research and tesing I was able to fix it. 

I added a few columns in my bridge table and filtered by the uniqe ID to sum up the values. 

f.e. Budget = CALCULATE(SUM(Budget[Budget]);FILTER(Budget;Budget[Req Tracking Nr]='Requsition Items'[Req Tracking Nr]))