Blank value in dynamic rank measure

[natabird3]

Hello,

I am trying to create a dynamic rank measure. I have country and spend columns. I want to rank all countries and display top 6 one by one in separate visuals by enforcing visual filter, select value which is equal to rank 1, rank 2, rank 3, etc. 6 different visuals. the problem i get is when i have a country with blank value. In those cases the measure that i created is not working as if i tell a visual to display rank 6 and there isnt any country there it simply stays blank. But i would like to display the first blank value (first country in the list) instead. I cannot find any measure that can display the first blank. it has the opposite function to display firstnoblank but nothing in the opposte way. Any ideas?

Thanks for help in advance.

[harshnathani]

Hi @natabird3 ,

Try these measures

Total Spend = SUM(Spend)

Rank = IF (NOT (ISBLANK([Total Spend])) , RANKX(Table, [Total Spend]))

Regards,

Harsh Nathani

[natabird3]

Actually, this does not seem to work as intended. Here is what I currently have that works fine:
Rank function =
VAR temp =
SUM ( cf[Actual spend EUR])
RETURN
IF(
temp = 0,
BLANK(),
RANKX (ALLSELECTED(COUNTRY), CALCULATE(SUM(cf[Actual spend EUR]) ) )
)

The only issue here is when I filter a rank and is blank. So if I force filter on a visual to display rank 4, and there isn’t a rank 4 for the time period selected only say 3 countries with spend. The 4th one should display blank and it does, however I want to display first blank value (country) and not simply leave as blank. Hope this explains better what trying to do.

[harshnathani]

Hi @natabird3 ,

Rank function =
VAR temp =
SUM ( cf[Actual spend EUR])
RETURN
IF(
temp <> 0,
RANKX (ALLSELECTED(COUNTRY), CALCULATE(SUM(cf[Actual spend EUR]) ) )

Regards,

Harsh Nathani

[natabird3]

This doesnt work as well because if you see when i try to rank 3, there isnt one country filter is blank. however the data in the table is showing the last value selected so is not displaying blank as it should. So i need to find a way to dispaly the first country with blank value and no rank if that makes sense.

[harshnathani]

Hi @natabird3 ,

please share sample data to help you on this.

Regards,

HN

[natabird3]

For example, look at 2020. We have three countries and if we were to rank them with current function, I would have rank 1 and rank 2 only. But if I forced a rank 3 on a visual and its blank I would like to display the first country with a blank value (in that case turkey). Hope this explains what I am trying to do. Here is some sample data:

Year	Brand	Country	Spend
2017	Name	Turkey	5,000
2017	Name	India
2017	Name	China	13,000
2018	Name	Turkey
2018	Name	India	8,000
2018	Name	China	12,000
2019	Name	Turkey	3,000
2019	Name	India	7,000
2019	Name	China
2020	Name	Turkey
2020	Name	India	8,000
2020	Name	China	10,000

[harshnathani · Accepted solution]

Hi @natabird3 ,

Rank = RANKX(Filter(ALL('Table'), 'Table'[Year] = MAX('Table'[Year])),CALCULATE(SUM('Table'[Spend])))

[natabird3]

I am not sure why but when i try to use a filter and select rank, it doesnt filter the table to display only the country with the corresponding rank:

[harshnathani]

Hi @natabird3 ,

You need to select the Table and then put the Visual Filter.

What i see from the image attached is that the filter is put on the Slicer.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)