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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 73 members 991 guests
Please welcome our newest community members: