I have data which contains customer, location and sales information similar to this:
| CustID | LocID | SaleID | Sales Amount |
| C0001 | L0001 | S0111 | 4500.5 |
| C0002 | L0001 | S0112 | 122.9 |
| C0003 | L0001 | S0113 | 3210 |
| C0004 | L0001 | S0114 | 2220 |
| C0005 | L0002 | S0115 | 595.5 |
| C0006 | L0002 | S0116 | 695 |
| C0007 | L0003 | S0117 | 1925.8 |
| C0008 | L0003 | S0118 | 2895 |
| C0009 | L0003 | S0119 | 1180.4 |
| C0010 | L0004 | S0120 | 1180.4 |
I am looking to Rank all distinct locations by [Sales Amount] descending and select Top % in set increments, selected in a slicer on the report and have this dynamic in that I can use other slicers and filters on the report page for instance that relate to the customer details or sales details to filter on, and the Top % ranking adjusts to those selection changes e.g. top 10% locations where the customers were a particular age group where I would select the top 10% in the ranking slicer, and age group in another slicer on the page listing customer age groups.
Here is an example of what the table in the report looks like:
| LocID | Latitude | Longitude | Number of Sales | Total Sales Amount | Total Sales Amount Rank |
| L0001 | 57.331432 | -2.8344727 | 5 | £10,053.40 | 1 |
| L0003 | 57.262429 | -2.6408386 | 3 | £6,001.20 | 2 |
| L0002 | 57.282476 | -2.8770447 | 2 | £1,290.50 | 3 |
| L0004 | 57.340327 | -2.6174927 | 1 | £1,180.40 | 4 |
I have created a measure like this to give me the ability to filter the table:
TopPercLoc =
IF( ISBLANK ( [Total Sales Amount] ) ,
BLANK(),
IF ( ( RANKX( ALL ( ‘Table‘ [LocID] ), [Total Sales Amount], ,DESC ) - 1
< ( CALCULATE( [DistinctLocIDs], ALLSELECTED(‘Table) ) * ‘Top %‘[Top%Value] ) ,
"True" ,
"False" ) )NB the '- 1' after the RankX is a workaround as I have some customers with blank locations in the data at the moment but had sales amounts, this data quality issues will likely be sorted in future but for now the blank location has the highest total sales amount ang was being ranked as 1 in my table visual. I did the - 1 so that the blank locations become rank 0 and the first valid location with the highest total sales amount gets ranked as 1 and so on. I tried to use another IF in the measure to exclude the blank locations but couldn't get it to work - if anyone can suggest a fix for this to exclude the blank locations then that would be appreciated (but not my main issue).
The other measures being used in my table visual just for context are:
[Total Sales Amount] = CALCULATE ( SUM (‘Table‘[Sales Amount] ) )
[DistinctLocIDs] = DISTINCTCOUNTNOBLANK(‘Table‘[LocID] ) )
The [TopPercLoc] measure returns True if a location is within the top % selection made and False if out with it. I added this measure to the table visual and then create a check measure which uses the CALCULATE( [DistinctLocIDs], ALLSELECTED(‘DataTable) ) * ‘Top %‘[Top%Value]. This gave me a value for the number of rows which are true based on selection and I could then scroll through the table to check that all rows to that row number showed as true and the rest showed false which the did. I then removed the [TopPercLoc] measure from the table visual and added it in as a filter on the visual instead setting it to include true only. This filtered out any of the rows showing false which is what I am looking for with the table and I figured that if I were to leave the measure in the table, it would set the top % of the top% already filtered down with true values and the rest with false so cant have it in the table visual and as the visual filter at the same time which is fine as my main aim was to show only the locations within the top % selected.
I am looking to add a map visual on the page next to the table to show the locations using their latitudes and longitudes and show the same as what appears in the table visual which is whatever top % sales amount rank is selected and the filtering from the other slicers select on the report page, the points on the map then will have size depending on the Sales Amount e.g. larger where higher sales.
The issue I am having is that I have tried to add the [TopPerfLoc] measure to the map visual as a filter and setting to filter to where "True" exactly the same as what I did with the table visual, but when i do this, the filter applies but the map visual has the swirling circle icon top right hand corner and just doesn't filter down to show only points on the map relating to the locations showing correctly in the table visual. When i filter by the other slicers on the page it does filter the points like it does with the table but the [TopPerfLoc] ranking part doesn't work.
Any ideas on where I am going wrong with this and the reason why it isn't working when I try to use it with the map? I'm not sure if my efforts at the measure are the best way of doing things its based on a few ideas that I tried out from what I had seen online already regarding RankX to have a go at it.
