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
tdjohnson7700
Frequent Visitor

Problems with Rankx skipping numbers

Sample PBIX File Hi–I am fairly new to Power Bi and I am not a programmer. I have searched extensively for this issue.

 

I have been developing a power bi file to show how different cities relate as to their population size–I have attached my pbix sample file.   The page showing Tjohnson working solution is showing how the visual is at this point–where you select a City, then the 5 cities in population larger than that city and the 5 cities in population below that city are shown, along with the selected city. This is all working–also shown is the Population Rank of each city within the state for the fiscal year selected, the population, total taxes, etc.

 

What is now the problem is that I have a table called tblCityPopulationByYear–this has a user relationship to the date table since my CitySalesTaxCollections has a direct connection to the date table. For each city, I have taken the census figure and entered the census amount for each month for the year. However, when you are calculating by Fiscal Year–if the census has changed, the amounts change from 1 year to the other. See examples below for City 101

City 101 FY 2021.png

In this case–the monthly population amounts for months 7/1/2020-6/30/2021 do not change, therefore the average population is the same as the population for each month. When this is the case, the chart works correctly as shown below.

city 101 5 above 5 below fy 2021.png

 

 However, on the fiscal years where the census has changed, it is not working correctly. See below for City 101 for Fiscal Year for months 7/1/2017-6/30/2018

 

City 101 fy 2018.png

On this page–the average population is calculating correctly. However, when placed on the chart for 5 up and 5 down, this is what happens.

 

city 101 5 above 5 below fy 2018.png

As you can see, the rank doubles, also only 5 are displaying since the difference between the ranks have changed.

The measures that are calculating the rank are as follows.

 

 

NORMALRANK = CALCULATE([Census Pop Rank FY Display],ALL(tbl1City))

 

This is branching from this measure

 

 Census Pop Rank FY Display = 
RANKX( ALL(tblCityPopulationByYear),[POP FY DISPLAY],,DESC,Dense)

 

The measure for the population for the chart is as follows:

 

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

 

This is branching from this measure

 

Pop FY = VAR FYPopAvg = AVERAGEX( VALUES( dDate[Fiscal Year] ), [Total Population by Census Date] )
VAR PopAverageTotal = SUMMARIZE (dDate, dDate[Fiscal Year], "FY Pop Avg", FYPopAvg )

RETURN
IF( HASONEVALUE( dDate[Fiscal Year] ),
    FYPopAvg,
        SUMX( PopAverageTotal, [FY Pop Avg] ))

 

Which is using this measure to calculate the average population.

 

Total Population by Census Date = 
CALCULATE(
[Average City Population],USERELATIONSHIP(tblCityPopulationByYear[CensusDate],dDate[Date]))

 

I have included screens for 2 cities within the chart for population for different fiscal years so you can see the amounts entered for each month.

 

I am fairly new to Power Bi and I am not a programmer. I have searched extensively for this issue. The rank works correctly if it is not comparing the 5 up and 5 down, so I know it has something to do with that visual. If anyone can please give assistance or point me in the right direction, I would appreciate it. I have modified the formula several times for the visual rank formula, but have not found the appropriate solution.

Thank you all in advance!

 

 

4 REPLIES 4
lbendlin
Super User
Super User

In your combo chart you use a solid line to connect cities together. That makes no sense from a data point of view as these cities are independent entities.  One way to solve it would be to make the markers more prominent but make the line itself hidden.

I believe you are speaking about the line for the population?  That just shows what the population is of each city, since that is what I am comparing-the sales tax of cities with population of 5 above and 5 below the population of the chosen city.  Do you have any idea of why the Rankx doubles the rank?

lbendlin
Super User
Super User

"I am fairly new to Power Bi and I am not a programmer" - yeah, but you have chosen a really hard problem to start your journey.  I would recommend you make extensive use of variables and CONCATENATEX to troubleshoot and debug every single step in your very complex calculation.  Probably a slipped filter context somewhere.

 

Also - please don't use a line to connect city data. Lines suggest correlation, and there simply is none in that data.

@lbendlin  Thank you so much for assisting me.  I used the CONCATENATEX to look at the formula for the average, and it appears that it is using the correct values for each city by month.  I am not sure where to go from here.  

 

Power Bi with ConctenateX.PNG

 

 

 

 

 

 

The Rank, however, is doubling when the population doesn't have the same number each month.  

 

Also--I am not understanding what you mean by "please don't use a line to connect city data. Lines suggest correlation, and there simply is none in that data"--can you please explain more?  

 

Thank you so much for your response.

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.

Top Solution Authors