Working with repeating values in a dataset

[ruliana]

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.

[v-gizhi-msft · Accepted solution]

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