Using column from another table for percentile calculation

[Anonymous]

Hi, I am trying to calculate percentiles for my sales (coming from 'coverage_revenue' table) but I need to use an ID column in another table ('table_b'). I understand I will need to use the RELATED function but not sure exactly where to use it.

The ID column exists in both tables (some IDs are missing from main table hence using the column from another table) and there is a relationship between both tables. 

The code is below, could someone help me with the correct usage of the RELATED function: the ID column that needs to change is 'Coverage Revenue'[RM_id] to ID column in another table.

Overall Percentile =

VAR SalesValue =

    [Total Sales]

RETURN

    IF(

        HASONEVALUE('Coverage Revenue'[RM_id]),

        COALESCE(

            DIVIDE(

               

                CALCULATE(

                    COUNTROWS('Coverage Revenue'),

                    FILTER(

                        ALL('Coverage Revenue'),

                        'Coverage Revenue'[Sum_NetRevPYFY] < SalesValue

                    )

                ),

               

                CALCULATE(

                    COUNTROWS('Coverage Revenue'),

                    ALL('Coverage Revenue')

                )

            ),

            0

        )

    )

[v-jayw-msft]

Hi @Anonymous ,

Please show some sample data and expected result.

You could check the topic about How-to-Get-Your-Question-Answered-Quickly 

Best Regrds,

Jay

[amitchandak]

@Anonymous , related work in 1-M relationship to bring data from 1 side to M side

refer this, if this can help

Relatedtable, Related -https://youtu.be/Wu1mWxR23jU

[Anonymous]

Thanks for the video link, helped me understand a few things but have not resolved my problem. 

Turns out the relationship between the two tables cannot be created between the ID columns because of a many to many relationship (both tables with the ID column have duplicate IDs). Is there any other way I can use the ID column from tableB over tableA in my rankx measure?