Calculating ratio if one value is smaller than another in 2 different table

[Anonymous]

Hello everyone, may I know how can i calcultae the the ratio when comparing the 2 column in different table ? for example, in PO table, if the "PO delivery date" is smaller than GRN date in GRN table. it will be consider as late delivery. and i would like to know the ratio of row item that is "late delivery" in PO table. thank you !

PO table

GRN Table

[tamerj1]

Hi @Anonymous 

do you have a relationship between the two tables? If not can you create one?

one more thing to notice. The dates seem to be of text data type!

[Anonymous]

hi, Yes. I have created relationship between both table. 
Okay ! have change to date already !. thank you !

[tamerj1]

Hi @Anonymous 

please try

late delivery % =
DIVIDE (
    SUMX (
        'PO Table',
        IF ( 'PO Table'[PO Delivery Date] < RELATED ( 'GRN Table'[GRN date] ), 1 )
    ),
    COUNTROWS ( 'PO Table' )
)

[Anonymous]

Hi, thanks for reaching out ! but not sure why it coult not found the GRN date. 

this is the relationshop i created between both table

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

According to your screenshot, I know you create a relationship between 'PO Table'[PO Number] and 'GRN Table'[PO Number]. Error should be caused by one to many relationship. I think there should be multiple duplicate PO Number in GRN table. Related function could only return a single value instead of multiple values. So you will get error. I suggest you to try Max function to get GRN Date.

I can reproduce the issue in my sample.

PO:

GRN:

Try this code to calculate the percentage of on time delivery.

on time delivery % = 
VAR _COUNT_ON_TIME =
    CALCULATE (
        DISTINCTCOUNT ( 'PO Table'[PO Number] ),
        FILTER (
            ALLSELECTED ( 'PO Table' ),
            'PO Table'[PO Delivery Date] >= CALCULATE ( MAX ( 'GRN Table'[GRN date] ) )
        )
    )
VAR _COUNTALL =
    CALCULATE (
        DISTINCTCOUNT ( 'PO Table'[PO Number] ),
        ALLSELECTED ( 'PO Table' )
    )
RETURN
    DIVIDE ( _COUNT_ON_TIME, _COUNTALL )

 Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-rzhou-msft ,

Thank you so much for your clarification, it does work now and your explanation is really helpful!

[tamerj1]

@Anonymous 

late delivery % =
DIVIDE (
    SUMX (
        'GRN Table',
        IF ( 'GRN Table'[GRN date] > RELATED ( 'PO Table'[PO Delivery Date] ), 1 )
    ),
    COUNTROWS ( 'PO Table' )
)

[Samarth_18]

Hi @Anonymous ,

Could you please share the expected output?

BR,

Samarth

[Anonymous]

Hi Samarth, thanks for yor answer in one of the question i share jn. (Solved: How to compare date in different table - Microsoft Power BI Community)

I think the problem i face now is that. I wanted to create a card in power Bi that can show the percentage of the line item that show "on time delivery". for exmaple, based on the picture below, there are 5 out of 10 row that show "On time". so, the card should be showing 50%. 

[Samarth_18]

@Anonymous Okay after creating that column,you could create a measure as below:-

Measure =
VAR total_row =
    COUNTROWS ( 'PO Table' )
VAR ontime_row =
    COUNTROWS ( FILTER ( 'PO Table', 'PO Table'[Column] = "On Time Delivery" ) )
RETURN
    DIVIDE ( ontime_row, total_row, 0 )

Note:- Kindly Mark this measure format as percentage.