cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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"

@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
Not applicable

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]

Highlighted
Community Support Team
Community Support Team

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

@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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)