Forum Discussion

estefaniamc's avatar
estefaniamc
Frequent Visitor
8 years ago
Solved

TOPN in graphs/Dynamic

Hi all,    I would like to display a bar graph only with top 5 values. I am using the advance filtering to show only the top values, but then when I filter in a different graph to show the top 5 va...
Tips and Tricks
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    here you will find a pbix file.

     

    I changed the DAX of the measure that ranks the customer (guess this is what you call Payer) to

    GroupRank = 
RANKX(
	ALLSELECTED(Table1[Customer])
	,CALCULATE(SUM(Table1[Amount]))
)

    And I also changed the Interaction Setting of the chart showing the Top 5 Customer to filter

    Mark the Chart showing the countries: Menu Format, Toggle: Edit Interactions, Switch the Customer chart from Highligh to Filter, Toggle: Edit Interactions

     

    Hope this is what you are looking for

     

    Regards

    Tom

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
8 years ago

estefaniamc,

 

As CahabaData said, you need to have a measure to rank your values, and then add this rank to your visual filter.

Create two measures.
TotalAmount = SUM(Table1[Amount])

GroupRank = RANKX(ALLEXCEPT(Table1,Table1[Group]),Table1[TotalAmount])

 

Regards,

Charlie Liao

 

  • estefaniamc's avatar
    estefaniamc
    Frequent Visitor
    8 years ago

    v-caliao-msft/CahabaData, 

     

    Thanks for your reply. Unfortunattely; I can´t make it work.

     

    I have two columns, one of them is Payer and the other one is the amount. I would like to see on a bar chart the top 5 payers by highest amount, I also would like that when I filter by date or Region, this graph is updated. & 

    I tried the formula you mentioned.

     

    TotalAmount = Sum(MyTable[Amount])

    GroupRank = RANKX(ALLEXCEPT(MyTable,MyTable[Payer Name]),sum(MyTable[TotalAmount]))

     

    Then I filtered on the graph GroupRank less or equal to 5 but when I filter by Region the bar chart doesn´t get updated.

     

    Please see data set sample below:

     

    RegionCountryCustomerAmount
    EMEAGermanyAA5
    EMEAItalyBB10
    EMEAFranceCC8
    EMEAGermanyDD20
    LATAMBrazilAA5
    LATAMArgentinaBB9
    LATAMMexicoCC15
    LATAMBrazilDD3
    LATAMColombiaEE5
    LATAMColombiaAA10
    NAUSFF15
    NACanadaCC12
    NAUSHH15
    NAUSEE32
    APChinaAA15
    APJapanJJ20
    APVietnamUU27
    EMEAFranceSS30
    LATAMArgentinaEE50
    APVietnamBB5
    APThailandJJ2
    APVietnamUU7

     

    Thanks in advance for your assitance,


    Regards,


    Estefania

      • TomMartens's avatar
        TomMartens
        Super User
        8 years ago

        Hey,

         

        here you will find a pbix file.

         

        I changed the DAX of the measure that ranks the customer (guess this is what you call Payer) to

        GroupRank = 
RANKX(
	ALLSELECTED(Table1[Customer])
	,CALCULATE(SUM(Table1[Amount]))
)

        And I also changed the Interaction Setting of the chart showing the Top 5 Customer to filter

        Mark the Chart showing the countries: Menu Format, Toggle: Edit Interactions, Switch the Customer chart from Highligh to Filter, Toggle: Edit Interactions

         

        Hope this is what you are looking for

         

        Regards

        Tom