Ranking is working when I exclude few sites from visual

[Anonymous]

Hi,

I have this issue with ranking, i.e. it is working fine until I have put a filter on the viasual.

I have a requirement to exclude few sites from the visual, so I have created a bridging table to get me all the sites that I want.

Now when I do the ranking all the excluded sites are summed and coming as blank in the visual.

Until now the ranking works fine, but when I try to exclude that blank sites from the visual the ranking doesn't work.

Any hlep is much appreciated.

Thanks in advance.

[tex628]

Is your ranking calculation a column or a measure? If it's a measure please provide the dax code!

Br,
J

[Anonymous]

Hi @tex628,

It is a measure and this the dax I am using to calculate the ranking.

RANKX(ALLSELECTED('Summarized Sites'),'YTD Performance'[Vs LY%],,Desc,Dense)

Regards,

U

[tex628]

The 'Summarized Sites' table, does it only contain the sites that you want to include?

[Anonymous]

Yes, That table contains all the sites that are Open.

This is the dax I am using to create the table 

SUMMARIZECOLUMNS(Sites[Franchise],Sites[Division],Sites[Site],FILTER(Sites,Sites[SiteStatus] = "Open"))

BR,

U

[tex628]

Are you using the site column from the summarized table as the row dimension in your table?

Are you getting incorrect ranks or blank rank values? 

[Anonymous]

I am using the Site column from the summarized table. 

And the ranking is based on the sales, since there are some sales that are available in the sales table and not in the summarized table, it is throwing blanks, but the ranking is working correctly until this point.

But when I try to remove the blanks from the visual by bringing the site column into the filter pane and excluding the blanks, the ranking is not working.

[tex628]

Sorry for shooting you with a million questions! I've recreated your scenario with your RankX calculated over the summarized table and everything is working for me...

[Anonymous]

Hi @tex628 ,

I really appreciate the time you have put into this.

I will try to create a PBIX file with dummy data and will post it here. 

And will see if where the issue is.

Thanks

[tex628]

If that's possible it would be wonderfull! 

[tex628]

How does the relationship look? 

Could you take a picture on how the ranks looks after you exclude?