cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michalmitosinka Frequent Visitor
Frequent Visitor

Dividing two measures from different tables always return "1"

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?
3 REPLIES 3
Community Support Team
Community Support Team

Re: Dividing two measures from different tables always return "1"

@michalmitosinka,

 

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.

michalmitosinka Frequent Visitor
Frequent Visitor

Re: Dividing two measures from different tables always return "1"

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]

Community Support Team
Community Support Team

Re: Dividing two measures from different tables always return "1"

@michalmitosinka,

 

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.