Forum Discussion

AyushAwasthi's avatar
AyushAwasthi
Helper I
1 year ago
Solved

Show rows having common elements

I have a "Final Data" table with columns: Company, CIK Code and Ratio. (Every row is unique) I have a "List Table" table with columns: Company (all values from company column in Final Data table). ...
  • v-sshirivolu's avatar
    v-sshirivolu
    1 year ago

    Hi AyushAwasthi ,

    Try these steps-

    Create Measures

    AveragePeerRatio =
    VAR SelectedCIKs =
    VALUES ( 'List Table'[CIK Code] )

     

    VAR SelectedBoardCodes =
    CALCULATETABLE (
    VALUES ( 'Match Table'[Board Code] ),
    'Match Table'[CIK Code] IN SelectedCIKs
    )

    VAR RelatedIndividuals =
    CALCULATETABLE (
    VALUES ( 'Board Data'[Individual Id] ),
    'Board Data'[Organization Id] IN SelectedBoardCodes
    )

    VAR PeerOrgs =
    CALCULATETABLE (
    VALUES ( 'Board Data'[Organization Id] ),
    'Board Data'[Individual Id] IN RelatedIndividuals
    )

    VAR PeerCIKs =
    CALCULATETABLE (
    VALUES ( 'Match Table'[CIK Code] ),
    'Match Table'[Board Code] IN PeerOrgs
    )

    RETURN
    CALCULATE (
    AVERAGE ( 'Final Data'[Ratio] ),
    'Final Data'[CIK Code] IN PeerCIKs
    )


    For PeerCompanyRank :

    PeerCompanyRank =
    VAR SelectedCIKs =
    VALUES ( 'List Table'[CIK Code] )

    VAR SelectedBoardCodes =
    CALCULATETABLE (
    VALUES ( 'Match Table'[Board Code] ),
    'Match Table'[CIK Code] IN SelectedCIKs
    )

    VAR RelatedIndividuals =
    CALCULATETABLE (
    VALUES ( 'Board Data'[Individual Id] ),
    'Board Data'[Organization Id] IN SelectedBoardCodes
    )

    VAR PeerOrgs =
    CALCULATETABLE (
    VALUES ( 'Board Data'[Organization Id] ),
    'Board Data'[Individual Id] IN RelatedIndividuals
    )

    VAR PeerCIKs =
    CALCULATETABLE (
    VALUES ( 'Match Table'[CIK Code] ),
    'Match Table'[Board Code] IN PeerOrgs
    )

    VAR SelectedRatio =
    CALCULATE (
    MAX ( 'Final Data'[Ratio] ),
    'Final Data'[CIK Code] IN SelectedCIKs
    )

    VAR PeerTable =
    FILTER (
    'Final Data',
    'Final Data'[CIK Code] IN PeerCIKs
    )

    VAR RankedTable =
    ADDCOLUMNS (
    PeerTable,
    "Rank", RANKX ( PeerTable, [Ratio], , DESC )
    )

    VAR MyRank =
    MAXX (
    FILTER ( RankedTable, [CIK Code] IN SelectedCIKs ),
    [Rank]
    )

    VAR TotalPeers = COUNTROWS ( PeerTable )

    RETURN
    FORMAT ( MyRank, "0" ) & "/" & FORMAT ( TotalPeers, "0" )



    Card Visuals

    1. Card for AveragePeerRatio.

    • Add a Card visual.

    • Set the Value to AveragePeerRatio.

      .

    2. Card for PeerCompanyRank.

    • Add another Card visual.

    • Set the Value to PeerCompanyRank.

     Add a Table Visual

    1. Add these fields from Final data :

      • Company 

      • CIK code

    Apply the Measure as a Filter

    1. With the table visual selected, go to the Filters pane on the right.

    2. Locate your new measure ShoqInPeerList.

    3. Drag ShowInPeerList into "Filters on this visual".

    4. Change the filter condition to:

      • "is"

      • "1"

     

    Measure -  ShowInPeerList =
    VAR SelectedCIKs =
    VALUES ( 'List Table'[CIK Code] )

    VAR SelectedBoardCodes =
    CALCULATETABLE (
    VALUES ( 'Match Table'[Board Code] ),
    'Match Table'[CIK Code] IN SelectedCIKs
    )

    VAR RelatedIndividuals =
    CALCULATETABLE (
    VALUES ( 'Board Data'[Individual Id] ),
    'Board Data'[Organization Id] IN SelectedBoardCodes
    )

    VAR PeerOrgs =
    CALCULATETABLE (
    VALUES ( 'Board Data'[Organization Id] ),
    'Board Data'[Individual Id] IN RelatedIndividuals
    )

    VAR PeerCIKs =
    CALCULATETABLE (
    VALUES ( 'Match Table'[CIK Code] ),
    'Match Table'[Board Code] IN PeerOrgs
    )

    RETURN
    IF ( 'Final Data'[CIK Code] IN PeerCIKs, 1, 0 )



    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

    Find the attached pbix file for your reference.

    Best Regards,
    Sreeteja.
    Community Support Team.

v-sshirivolu's avatar
v-sshirivolu
Community Support
1 year ago

Hi AyushAwasthi 
Thanks for reaching out to the Microsoft fabric community forum.


Try this steps -

Create a Measure for Selected Company :

Selected Company = SELECTEDVALUE('List Table'[Company])


Create a Measure to Identify Peer Companies :

Is Peer Company =
VAR TargetCompany = [Selected Company]
VAR TargetIndividuals =
CALCULATETABLE (
VALUES ( 'Match Table'[Individual ID] ),
'Match Table'[Company] = TargetCompany
)
VAR CurrentCompanyIndividuals =
CALCULATETABLE (
VALUES ( 'Match Table'[Individual ID] ),
'Match Table'[Company] = MAX('Final Data'[Company])
)
VAR SharedIndividuals =
INTERSECT ( TargetIndividuals, CurrentCompanyIndividuals )
RETURN
IF ( COUNTROWS(SharedIndividuals) > 0, 1, 0 )


Create Measure for Average Ratio of Peer Companies :

Avg Ratio of Peers =
CALCULATE (
AVERAGE ( 'Final Data'[Ratio] ),
FILTER (
'Final Data',
[Is Peer Company] = 1
)
)

Create Rank of Selected Company Among Peers :

Rank of Selected Company =
VAR TargetCompany = [Selected Company]
VAR PeerCompanies =
FILTER (
'Final Data',
[Is Peer Company] = 1
)
RETURN
RANKX (
PeerCompanies,
CALCULATE(SELECTEDVALUE('Final Data'[Ratio])),
,
DESC,
DENSE
)

Count Number of Peer Companies :

Peer Company Count =
CALCULATE (
DISTINCTCOUNT('Final Data'[Company]),
FILTER (
'Final Data',
[Is Peer Company] = 1
)
)

How Rank as a Text (e.g., 3/5) :

Rank Text =
VAR RankVal = [Rank of Selected Company]
VAR Total = [Peer Company Count]
RETURN
FORMAT ( RankVal, "0" ) & "/" & FORMAT ( Total, "0" )

Find attached Pbix file for your reference.

 

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team