How to consolidate data from 2 tables

[FOliveira]

Hi everyone,

I am stil giving my first steps with PBI.

I am making a report that monitors the Purchased Licenses vs. Assigned licenses for each department.

Can you please help me in getting the following data consolidated?

Source tables:

TBL PURCHASES
Part Nr	Dept	QTY
A	CL	10
B	CL	5
C	ES	15
B	ES	10
A	CL	5

TBL ASSIGNMENTS
Part Nr	Dept
A	CL
B	ES
D	ES
A	CL
B	ES

Consolidated table:

TBL SUMMARY
Part Nr	Dept	QTY PURCHASED	QTY ASSIGNED *	BALANCE
A	CL	15	2	13
B	CL	5	0	5
C	ES	15	0	15
B	ES	10	2	8
D	ES	0	1	-1

* Assigned quantity is the Count of "Part Nr" for each "Dept"

Your help would be much appreciated.

Thank you in advance,

Fernando

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[FOliveira]

Hi again Hashish,

Can you please explain this formula, and what is the coalesce function?

Quantity assigned = coalesce(COUNTROWS(Purchases),0)

The formula 

Quantity purchased = coalesce(SUM(Purchases[QTY]),0)

Makes sense

Finally, do you have a solution to remove the lines with 0 Purchases and 0 Assignments?

Thank you

[Ashish_Mathur]

Hi,

Please red up here. - COALESCE function (DAX) - DAX | Microsoft Docs.  Use filters to remove unwanted rows.

[FOliveira]

Hi Hashish,

Thank you very much for putting me back on track.

I was very close, but I messed up in the relationship.
Your very simple schema made it for me 100%
Thank you once again,

Fernando

[Ashish_Mathur]

You are welcome.  Please mark my previous reply (the reply which answered your question) as Answer.

[ryan_mayu · Accepted solution]

@FOliveira 

1. use PQ to create a dim table to get all the combination of PART and DEPT

2. create measures

QTy purchase = sumx(FILTER('PURCHASES','PURCHASES'[Part Nr]=max('Append1'[Part Nr])&&'PURCHASES'[Dept]=max('Append1'[Dept])),PURCHASES[QTY])+0

qty assigned = COUNTAX(FILTER('ASSIGNMENT','ASSIGNMENT'[Part Nr]=max('Append1'[Part Nr])&&'ASSIGNMENT'[Dept]=max('Append1'[Dept])),'ASSIGNMENT'[Dept])+0

qty assigned = COUNTAX(FILTER('ASSIGNMENT','ASSIGNMENT'[Part Nr]=max('Append1'[Part Nr])&&'ASSIGNMENT'[Dept]=max('Append1'[Dept])),'ASSIGNMENT'[Dept])+0

pls see the attachment below

[FOliveira]

Hello Ryan,

It turns out that your sollution works better for me.

Thank you very much.

I have one doubt: In my report I have a slicer based on a "Department table"
So, I added the table Department to your model, established a One to Many relation with the Append1 table, but it is not filtering.

Any solution for this, please?
Thank you,

Fernando

[ryan_mayu]

@FOliveira 

append 1 is already a dim table.

you can use the dept column in that table to filter.