Filter issue

[ITManuel]

Hi all,

I have the following 3 tables:

"PCosts" is my fact table for project specific costs, "PList" my dimension table via which I'm filtering the fact table.  

In the fact table "PCosts" there are also Project additions (PA's) which do not appear in the "PList" table but which have to be considered also for certain projects. The table "Padd" defines how these PA's and their related costs would need to be considered for the project specific cost analysis i.e. to which projects they belong to. 

For example the total cost for P1 would be:

523

but for P2: 448+523+448+523+5662+432 = 7996

Is there a way that the PA's are automatically considered for the cost summary as per the table "Padd"?

A pbix test file is availabe under the following link.

https://we.tl/t-pTV0Ul20tI

Thanks in advance

Best regards

[ITManuel]

Shame on me. 🙈

Thanks a lot.

[HotChilli · Accepted solution]

The combination is incorrect.

You must join ProjectID into Padd-valued Pcosts (taking advantage of unpivot)

[HotChilli]

I think you could Unpivot the columns in  Padd.

Merge Padd and Pcosts to bring the ProjectID from Padd into Pcosts.  Add a column - if 'themergedcolumn' is null use ProjectID else 'themergedcolumn'. Call the column MasterProject.

It'll look like this

Then in powerbi Model view you can have a 1:m between ProjectID in Plist and MasterProject in Pcosts.

The project cost is a simple SUM from there.

Good luck.

[ITManuel]

@HotChilli ,

thanks for your respone.

I have issues to implement your proposed solution. It do not get a suitable merged table, from what I understand. This is what I did.

1. Original Padd Table

2. Unpivoted Padd Table

3. Original PCosts Table 

4. Merging Pcosts with Padd

5. Merged Table

In this merged table, I get 25 rows instead of the 20. I tried what you suggested with an additional column on this table but cannot get to the result as per your table. 

What am I doing wrong?

Best regards