Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
estefaniamc
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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

15 REPLIES 15
v-caliao-msft
Employee
Employee

@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

 

@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

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

 

THanks


Regards

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hello Sir, 

I am using year based slicer and expected result would be Last 5 year sales based on Year slicer selected value. Since I am using tabular model so i have limitation ; i can't use some date like SELECTEDVALUE, etc in my solution. 

 

Please help me 

 

https://community.powerbi.com/t5/Desktop/Sales-over-the-year-with-Dynamic-year-value/m-p/481273#M224...

 

Anonymous
Not applicable

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.

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hey @Ashish_Mathur,

 

Can you please help me with the rank formula syntax? Because the values on the charts are added based on unique supplier/parts/dealer number and day groups. 

 

formula is like= for each supplier number, count (>14)/ Grand total (>14) + count (8-14)/ Grand total (8-14)  + count (2-7)/ Grand total (2-7) + count (0-1)/ Grand total (0-1)  

 

Thanks

Hi,

 

Share the link from where i can download your file.  In a simple Table, also show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hey Ashish,

 

Sorry for the delay. I am attaching the link herehttps://drive.google.com/open?id=0B3RVPzvmX8RMa3FiTXVodXhyMDVyMVExNGczZG83MFpfcmUw. What I need is, instead of the stacked bar charts showing all the parts/supplier/dealer numbers, I just want top 10 of them to show based on the Percentage Grand total Count of Customer Order ID. This same parameter: %GT Count of System_CustomerReferenceOrderID is used as a value in all the stacked charts. 

Hi,

 

I cannot download the file from there.  I get an access denied message.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Anonymous
Not applicable

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.

Many thanks Tom!! It works now!!

CahabaData
Memorable Member
Memorable Member

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.