cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
estefaniamc Frequent Visitor
Frequent Visitor

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 values by region, the graph still shows the same top ones as before and it shouldn´t. Is there a way to make it work? 

first.JPG

 first2.JPG

 

Many thanks

 

Regards

 

Estefania

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: TOPN in graphs/Dynamic

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

2017-10-03_16-38-35.png

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

15 REPLIES 15
CahabaData New Contributor
New Contributor

Re: TOPN in graphs/Dynamic

Do you have a measure using the RANKX function?

 

If not, that is probably what you need - if I understand your post.  There are some good tutorials & Q/A on this function.  

 

If you need further assist, provide a short sample of your table.

 

 

www.CahabaData.com
Moderator v-caliao-msft
Moderator

Re: TOPN in graphs/Dynamic

@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])

Capture.PNGCapture1.PNGCapture2.PNG

 

Regards,

Charlie Liao

 

estefaniamc Frequent Visitor
Frequent Visitor

Re: TOPN in graphs/Dynamic

@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

estefaniamc Frequent Visitor
Frequent Visitor

Re: TOPN in graphs/Dynamic

Can anyone please advise? @v-caliao-msft/@CahabaData, ?

 

THanks


Regards

Super User
Super User

Re: TOPN in graphs/Dynamic

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

2017-10-03_16-38-35.png

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

estefaniamc Frequent Visitor
Frequent Visitor

Re: TOPN in graphs/Dynamic

Many thanks Tom!! It works now!!

niketmistry7 Frequent Visitor
Frequent Visitor

Re: TOPN in graphs/Dynamic

Capture.PNGHello Guys,

 

Is there a way I can only show top 10 Supplier Numbers (Y Axis) in the graph based on the % Grand Total Count of System Customer Number? Basically I just want to see top 10 bars in this graph. I am not able to use top N filter in Y axis value inside visual filters because it's changing the % values on the graph.

Highlighted
niketmistry7 Frequent Visitor
Frequent Visitor

Re: TOPN in graphs/Dynamic

Capture.PNGHello Guys,

 

Is there a way I can only show top 10 Supplier Numbers (Y Axis) in the graph based on the % Grand Total Count of System Customer Number? Basically I just want to see top 10 bars in this graph. I am not able to use top N filter in Y axis value inside visual filters because it's changing the % values on the graph.

Super User
Super User

Re: TOPN in graphs/Dynamic

Hi,

 

Create a RANK measure and then drag that measure in the visual filter with a condition of <=10.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 47 members 979 guests
Please welcome our newest community members: