Forum Discussion
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].
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.
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]))And then create the measure:
Measure = SUM('Table'[Rejects]) / (SUM('Table'[Rejects])+SUM('Table'[Good Parts]))It shows:
Hope this helps.
Best Regards,
Giotto Zhi
1 Reply
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]))And then create the measure:
Measure = SUM('Table'[Rejects]) / (SUM('Table'[Rejects])+SUM('Table'[Good Parts]))It shows:
Hope this helps.
Best Regards,
Giotto Zhi