cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ruliana
Helper I
Helper I

Working with repeating values in a dataset

I am working with two sets of data shown below, one of which contains mush more detailed scrap reasons. I want to make a measure to calculate the scrap percentage per month using the rejects for each reason from the scrap data and the good parts from the main production data (SUM[Rejects] / SUM[Rejects]+SUM[Good_Parts].

 

Scrap and Prod Pic .png

The [Good Parts] comes from the scrap data table shown below, and it is much higher than the actual [Good_Parts] which comes from another dataset since the numbers are repeated for multiple reasons each day. I need the values from the bottom table to be used as the good parts instead of what it's doing now where it's using the overall sum for each month. I cannot use a relationship for date between the datasets because right now it is set to relate the Prod Codes.Scrap Data Example 020520.png

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

According to your description, please try to create a month dimension table to build relationships among these tables:

Month dimension table = DISTINCT(SELECTCOLUMNS('Date',"Month",'Date'[Month]))

91.PNG

And then create the measure:

Measure = SUM('Table'[Rejects]) / (SUM('Table'[Rejects])+SUM('Table'[Good Parts]))

It shows:

92.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

View solution in original post

1 REPLY 1
v-gizhi-msft
Community Support
Community Support

Hi,

 

According to your description, please try to create a month dimension table to build relationships among these tables:

Month dimension table = DISTINCT(SELECTCOLUMNS('Date',"Month",'Date'[Month]))

91.PNG

And then create the measure:

Measure = SUM('Table'[Rejects]) / (SUM('Table'[Rejects])+SUM('Table'[Good Parts]))

It shows:

92.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors