Getting a measure to count specific items based on matrix drilldown.

[msciarrino]

Hello All,

I am attempting to create a Matrix that will show how many items we need to order, and how many items we actually ordered. Everytime we order a parent tool, all the children need to be ordered as well. That is what I am trying to verify.

My data is split into 2 tables, one with a list of all the placed orders (Parent and Child) and one with what we should order (grouped by parent tool). The table relationship is based on the Parent Item. This is a many to many relationship currently because it is possible that a parent item can show up multiple times in the Order Table.

In my Matrix, I can get to a point where I start with the Purchase Order Number, Drill down to the Parent Tool, then list the children out and list the quantity of parts needed. My problem comes when I try to show the quantity of parts ordered.

For that value, it is showing the count of the total items ordered not the count of each individual item ordered.

I am using a measure for the quantity ordered, but I am guessing I need to filter it somehow? Everything I have tried has got me nowhere.

Sample Data:

How my current Matrix looks:

How I want my Matrix to look:

Quantity Needed Measure:

QNeed = Count('Order Table'[Child Item])

Thank you for helping

[msciarrino]

Thank you so much! That worked like a charm!

[Daniel29195 · Accepted solution]

@msciarrino 

use this measure for 

Quantity Needed Measure:

QNeed = 

calculate(

Count('Order Table'[Child Item]),

treastas( values(need_table[parent]) ,   order_table[parent]),

treastas( values(need_table[child]) ,   order_table[child])

)

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠