Forum Discussion

ViralPatel212's avatar
ViralPatel212
Icon for Resolver I rankResolver I
4 months ago
Solved

TOP N affected by Legend in Bar Graph

Hi All,

 

I have created a disconnected TOP N table where its got my All my Dealers + a single row called Others, and this has a relationship with the main table Dealer to Dealer.

Relationship:

I have also a Disconnected TopN table with numbers: 

 

when adding a stacked clustered chart the with Dealer from Counterparty List 2 in the x axis and a TOPN Measure in the Y axis the visual works: IF i select 3 it will show me 3 dealers + Other. (Perfect)

Metrics Measure: 

Metrics: SUM('Dealer Ranking'[Commission LC - Everyone])
Top N Spend = 
VAR TOPNSelected = SELECTEDVALUE('Top N'[TopN])
VAR CurrentDealer = SELECTEDVALUE('Counterparty List 2'[Dealer])      
VAR TopDealerTable = 

    TOPN(
        TOPNSelected,
        ALLSELECTED('Counterparty List 2'),
        [Metrics]
    )


VAR TopDealerComission = 
    CALCULATE(
        [Metrics],
        KEEPFILTERS( TopDealerTable )
    )

VAR OtherCommission= 
    CALCULATE(
            [Metrics],
            ALLSELECTED('Counterparty List 2')
    ) - 
    CALCULATE(
        [Metrics],
        TopDealerTable
        )

 

RETURN
IF(
    CurrentDealer <> "Others",
    TopDealerComission,
    OtherCommission
)

The issue i am facing is when i introduce a Legend to the visual ( Participant) that is in the Dealer Ranking table, the TOP N breaks and it shows more: 

 

I need help in fixing this.

 

Thank You.

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • cengizhanarslan's avatar
    cengizhanarslan
    4 months ago

    When you add Participant to the Legend, the visual evaluates your measure for every Dealer + Participant combination. If you do not want it to affect the visual you could try using REMOVEFILTERs() inside your measure:

    Top N Spend =
VAR _TOPNSelected =
    SELECTEDVALUE ( 'Top N'[TopN] )
VAR _CurrentDealer =
    SELECTEDVALUE ( 'Counterparty List 2'[Dealer] )
VAR _TopDealerTable =
    TOPN (
        _TOPNSelected,
        ALLSELECTED ( 'Counterparty List 2' ),
        CALCULATE (
            [Metrics],
            REMOVEFILTERS ( 'Dealer Ranking'[Participant] )
        )
    )
VAR _IsTopDealer =
    COUNTROWS (
        INTERSECT (
            VALUES ( 'Counterparty List 2'[Dealer] ),
            SELECTCOLUMNS ( _TopDealerTable, "Dealer", [Dealer] )
        )
    ) > 0
VAR _TopDealerCommission =
    CALCULATE (
        [Metrics],
        KEEPFILTERS ( _TopDealerTable )
    )
VAR _OtherCommission =
    CALCULATE (
        [Metrics],
        ALLSELECTED ( 'Counterparty List 2' )
    ) -
    CALCULATE (
        [Metrics],
        _TopDealerTable
    )
RETURN
    IF (
        _CurrentDealer <> "Others",
        IF ( _IsTopDealer, _TopDealerCommission ),
        _OtherCommission
    )

5 Replies

  • DanieleUgoCopp's avatar
    DanieleUgoCopp
    Icon for Super User rankSuper User
    4 months ago

    Hello,

    You could try using ALL or REMOVEFILTERS on the Participant field inside the ranking/TOPN calculation, so the ranking ignores the legend context but still displays the split in the chart. Sometimes creating a separate ranking measure also helps keep the TOPN stable.

    Best regards,
    Daniele

    • ViralPatel212's avatar
      ViralPatel212
      Icon for Resolver I rankResolver I
      4 months ago

      Hi

       

      thanks for the suggestion: im still confused on how to do that? do i amend the current measure?

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    4 months ago

    When you add Participant to the Legend, the visual evaluates your measure for every Dealer + Participant combination. If you do not want it to affect the visual you could try using REMOVEFILTERs() inside your measure:

    Top N Spend =
VAR _TOPNSelected =
    SELECTEDVALUE ( 'Top N'[TopN] )
VAR _CurrentDealer =
    SELECTEDVALUE ( 'Counterparty List 2'[Dealer] )
VAR _TopDealerTable =
    TOPN (
        _TOPNSelected,
        ALLSELECTED ( 'Counterparty List 2' ),
        CALCULATE (
            [Metrics],
            REMOVEFILTERS ( 'Dealer Ranking'[Participant] )
        )
    )
VAR _IsTopDealer =
    COUNTROWS (
        INTERSECT (
            VALUES ( 'Counterparty List 2'[Dealer] ),
            SELECTCOLUMNS ( _TopDealerTable, "Dealer", [Dealer] )
        )
    ) > 0
VAR _TopDealerCommission =
    CALCULATE (
        [Metrics],
        KEEPFILTERS ( _TopDealerTable )
    )
VAR _OtherCommission =
    CALCULATE (
        [Metrics],
        ALLSELECTED ( 'Counterparty List 2' )
    ) -
    CALCULATE (
        [Metrics],
        _TopDealerTable
    )
RETURN
    IF (
        _CurrentDealer <> "Others",
        IF ( _IsTopDealer, _TopDealerCommission ),
        _OtherCommission
    )
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 months ago

    Hi,

    What result are you expecting when you drag Participant group to the legend?  Please also share the download link of the PBI file.