Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Using column from another table for percentile calculation

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

        )

    )

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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?