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
msciarrino
Helper I
Helper I

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

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:

msciarrino_0-1706637762579.png

How my current Matrix looks:

msciarrino_1-1706637797323.png

How I want my Matrix to look:

msciarrino_2-1706637817214.png

 

Quantity Needed Measure:

QNeed = Count('Order Table'[Child Item])
 
Thank you for helping
1 ACCEPTED SOLUTION
Daniel29195
Super User
Super User

@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! 🤠

View solution in original post

2 REPLIES 2
msciarrino
Helper I
Helper I

Thank you so much! That worked like a charm!

Daniel29195
Super User
Super User

@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! 🤠

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.