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

      • estefaniamc's avatar
        estefaniamc
        Frequent Visitor
        8 years ago

        Many thanks Tom!! It works now!!