Dividing two measures from different tables always return "1"

[Anonymous]

Hello,

I'm want to have percentage calculated from 2 tables (WNRC, EATR). I'm dividing two measures and result is always 1. When I create measures separately they return 2 correct, different values. I have set up many to many relation between these two tables.

WNRC Scrap % = DIVIDE(CALCULATE(COUNTA(WNRC[OSS_INVOICE_BAKEDATE_BILL_REASON_ID])|FILTER(WNRC|WNRC[OSS_INVOICE_BAKEDATE_BILL_REASON_ID]="NRC-SCRAP"))|SUM(EATR[TTL_QTY]))

Can somebody please advice whant could be issue?

[v-yuta-msft]

@Anonymous,

Could you share some more details about the table structure, relationship and what does the second measure look like?

Community Support Team _ Jimmy Tao

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

[Anonymous]

Hi @v-yuta-msft,

Measure I mentioned is already final measure comprised of two original ones. First measure is

      SCRAP QTTY=CALCULATE(COUNTA(WNRC[OSS_INVOICE_BAKEDATE_BILL_REASON_ID])|FILTER(WNRC)|WNRC                                                            [OSS_INVOICE_BAKEDATE_BILL_REASON_ID]="NRC-SCRAP"))

and second one is

      EATR QTTY=SUM(EATR[TTL_QTY])

Tables WNRC and EATR are linked through EATR number, each line in both tables have this information, eatr number can be same for several lines.

I need to calculate percentage of cases from table WNRC where column [OSS_INVOICE_BAKEDATE_BILL_REASON_ID]="NRC-SCRAP" from total EATR qtty (from table EATR). 

for table WNRC 1 line = 1 quantity

for table EATR 1 line = quantity in column [TTL_QTY]

[v-yuta-msft]

@Anonymous,

Suppose Table 'EATR' is a dimensional table, to reference 'EATR' table in fact table, you may modify your first measure like pattern below:

SCRAP QTTY =
CALCULATE (
    COUNTA ( WNRC[OSS_INVOICE_BAKEDATE_BILL_REASON_ID] ),
    FILTER (
        WNRC,
        RELATED ( EATR[number] )
            && WNRC[OSS_INVOICE_BAKEDATE_BILL_REASON_ID] = "NRC-SCRAP"
    )
)

Community Support Team _ Jimmy Tao

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