Usage Rate

[Anonymous]

Need some help please.  I have two table's, joined on DOC_NO.  The top table in the image will always have one line per DOC_NO, which lists each "Unit PartNumber" being worked.  The bottom table will have multiple lines per DOC_NO, which show detail parts that were used to overhaul the "Unit PArtNumber".

I'm trying to show the usage rate % of the detail parts for the "Unit PartNumber".  

Detail Parts G726681 was used 100% of the time in this example.  

Detail Parts G726421 was used 50% of the time in this example. 

How do I write measures to accomplish displaying the 100% and 50% of these two detail parts?

I believe it will involve countrows.

I've included an image of how the result should display.

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you should be able to follow steps below to get the expected result in your scenario.

1. Make sure the the cross filter direction is set to Both for the relationship between these two tables.

2. Use the following formulas to create three measures to calculate the usage rate % of the detail parts for the "Unit PartNumber".

DetailPartCount = CALCULATE(COUNT(Table2[Detail Parts]))

DocNoCount =
VAR currentUnitPartNumber =
    FIRSTNONBLANK ( Table1[Unit PartNumber], 1 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[DOC_NO] ),
        ALL ( Table2[Detail Parts] ),
        Table1[Unit PartNumber] = currentUnitPartNumber
    )

UsageRate = DIVIDE([DetailPartCount],[DocNoCount])

Note: you will need to replace Table1 and Table2 with your real table name.

Here is the sample pbix file for your reference. 

Regards

[Anonymous]

Thank you for your help.  I got everything working like it should now.

[Anonymous]

Try these measures (I create each one separately, so you could see how they are built, but you could put them in variables as well):

DetailPartCount = CALCULATE(count(DetailParts[DetailParts]))

DocNoCount = CALCULATE(DISTINCTCOUNT(DetailParts[DOC_NO]),all(DetailParts[DetailParts]))

UsageRate = DIVIDE([DetailPartCount],[DocNoCount])

[Anonymous]

Still trying to figure this one out.  I just can't get it to work correctly.  Heading home for the evening.  I'll be back tomorrow to update what I am seeing.

Thanks for the help.