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
Highlighted
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]

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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,684)