Selecting 5 above and 5 below dynamically

Hi. I posted a while back about 5 above and 5 below with RankX. A link to the posting is 5 above and 5 below a selected rank with RANKX - #23 by tjohnson jbressan was able to assist me with the selection of selecting the city that was in question and being able to show those cities 5 above and 5 below. However, I have to go and see which rank a City is and select that rank–I cannot select a city by name–and then it dynamically show the rank and select those cities 5 above and 5 below. The pbix file is attached.

If anyone could assist me with this, I would appreciate it.

Also–I am trying to put the tax rate for each city for the dates and sales tax type in question (only tax type 1 (Sales Tax) and 2 (Use Tax) have rates, the other types do not) I have included a table with the tax rates included in the table Rate Change History–as these change over time, I would like to show the current average rate for that fiscal year or calendar year that is being displayed. I have a measure that calculates the average rate–however, it does not connect with the visuals that I am needing them on that is organized by the population rank. I hope I was able to make this clear as to what I am trying to achieve. Please let me know if you need more information. Thank you all in advanceRank 5 above and 5 below for Enterprise DNA Help - JoseBressan (2).pbix|attachment (9.3 MB)

Hi @tjohnson, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

image

  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

Hi @tjohnson, we’ve noticed that no response has been received from you since the 19th of April. We are waiting for the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

More information for my model. I want to compare cities with similar populations. So right now, I need to go to the Sales Rank page and find the city that I want to compare to and see what their population rank is amongst the other cities–For example, first I choose which type of tax I want to compare–(some cities do not have all types of taxes, therefore, their rank will change based on the type of tax.) City 500 is Rank 22 for those that collect Sales Tax–so If I want to compare City 500, I note that it is rank 22. Then I go to the 5 above and 5 below and choose that rank–with the assistance I had from JBressan, He has created for me several measures, one for display row, one for Select City and one for SelectedCity, one for ShorRank and one for ShowRankValue, they are included in the model. Basically, the display row will show the 5 above and 5 below rank of the selected rank.

On the Choose Rank page, choose the City you want to compare. Then take note of the rank of the City. on the 5 above and 5 below sales tax page, choose the rank of the City that you are comparing. This will display those cities that are 5 above and 5 below the population rank for the desired City. This works perfectly.

There are 2 items I am needing assistances on.

  1. I would like to be able to choose a city (such as City 500) without having to find its rank–is there a way to do this dynamically so a user only has to choose which City they want to compare, no t the rank of the City.

  2. I also would like to show the average of the Sales Tax or Use Tax rate for the cities selected–These change over time. I have a table Rate Change History included–however, when I created a measure AverageTaxRate
    AverageTaxRate = AVERAGE('Rate Change History'[Rate])
    it shows the average of ALL cities–not the average rate of each individual city in the grid for the time period selected. I have included a page showing Rates where you can see that the cities that are being currently displayed in the Sales Tax page are shown–so not all the rates are the .03. I am not sure how to link the Rate Change History table to the model so that it works correctly. I am very much a beginner–so if I am not giving enough information, please let me know.

Any assistance would be greatly appreciated! We want this data to be as dynamic as possible so that the user can just select the City that they want to compare and the data be populated.

Rank 5 above and 5 below for Enterprise DNA Help - question of dynamic selecting city and showing rate average.pbix (9.1 MB)

1 Like

Hi @tjohnson

For your first query, I have created a Solution as attached. EDNA Solution - Rank 5 above and 5 below.pbix (9.1 MB)

Check page “Choose City - Sales Tax”, Here I am using City as Filter and based on selected city, Rank is calculated. Visual display city with before and after Ranked 5 cities. I have matched with your report and it seems correct.

For this, I have created a new Display Rank as

RANK DISPLAY =
VAR SELCITY =
SELECTEDVALUE ( tbl1City[Municipality] )
VAR SELRANK =
CALCULATE (
[Census Pop Rank FY],
ALL ( tbl1City ),
FILTER (
ALL ( tblCityPopulationByYear ),
tblCityPopulationByYear[Municipality] = SELCITY
)
)
VAR NORMALRANK =
CALCULATE ( [Census Pop Rank FY], ALL ( tbl1City ) )
RETURN
IF ( NORMALRANK >= SELRANK - 5 && NORMALRANK <= SELRANK + 5, 1, 0 )

and to display values in Visuals, need to modify measures as below. These needs to be done for all your measures.

POP FY DISPLAY =
CALCULATE ( [Pop FY], ALL ( tbl1City ) )

Check if this is helpful and what you are looking for. If not, suggest what changes are required.

For 2nd Query, raise a new Ticket as this is open for long and others won’t be able to check this.

Thanks
Ankit J

EDNA Solution - Rank 5 above and 5 below-tjohnson working solution.pbix (9.1 MB)
Ankit J
@ankit
Thank you SO much for assisting me with this!! I am currently working through changing formulas so that it can accommodate your solution.

One item I am still having problems with is the Sales Tax Per Capita—It is only showing the Sales Tax Per Capita for the selected city.

I am also having problems figuring out how to display the rank of the selected city–prior to this, I used the ShowRank and the ShowRankValue–which is how you used to choose which city would be selected. If you can give me some assistance with these two items, I would really appreciate it!

Attached is my working copy–the tab tjohnson Working Solution is what I am referring to.

Thank you again for your time and assistance.

Hi @tjohnson, just a friendly reminder, if your original question has been answered within the forum it is important to mark your thread as ‘solved’.

If you have a follow question or concern related to this topic please start a new topic.

More details can be found here - Asking Questions On The Enterprise DNA Support Forum.

1 Like

Ankit I figured out how to make the Sales Tax Per Capita show for every line. I studied your measure and created a new measure like so

`Sales Tax Per Capita Display = CALCULATE( [Sales Tax Per Capita],ALL(tbl1City))``

I was also able to modify one of your measures to display the selected city rank.

RANK DISPLAY 2 = VAR SELCITY = SELECTEDVALUE(tbl1City[Municipality])
VAR SELRANK = CALCULATE([Census Pop Rank FY],ALL(tbl1City),FILTER(ALL(tblCityPopulationByYear),tblCityPopulationByYear[Municipality] = SELCITY))
Return SELRANK

I think the first part of this question is answered! I really appreciate your assistance! I will post another questions in regards to the sales tax rate.

EDNA Solution - Rank 5 above and 5 below-tjohnson working solution.pbix (9.1 MB)