Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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