I have customer, location, and sale tables of data which when related in Power BI give similar sample data like the example below. For simplicity, I have just included the primary keys for customer and location and then the Sales key and amount which is of interest for this query - The location isn’t exclusive to the customer, it relates to the sale location which could be the same for multiple customers:
I am looking to Rank all distinct locations by SalesAmount descending and then be able to select a Top % in set increments which can be selected in a slicer on the report. 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 down the data, and the Top % ranking adjusts to those selection changes.
I have attempted this creating a measure something like this:
TopPercLoc = IF( ISBLANK ( [TotalSalesAmount] ) , BLANK(), IF ( ( RANKX( ALL ( ‘LocationTable‘ [LocID] ), [TotalSalesAmount], ,DESC ) - 1 < ( CALCULATE( [DistinctLocIDs], ALLSELECTED(‘CustomerTable) ) * ‘Top %‘[Top%Value] ) , ‘Top %‘[Top%Value] , BLANK() ) )
‘Top %‘[Top%Value] refers to a table created from the ‘New Parameter’ option to generate a series of disconnected values which range from 0 to 1 in 0.1 increments to use in a slicer to represent the Top % selected on the report e.g. Top 10% which will equate to the number of distinct locations selected based on any filters on the report based on customer details X % value selected in the Top % Slicer.
[TotalSalesAmount] = CALCULATE ( SUM (‘Sales‘[SalesAmount] ) ) [DistinctLocIDs] = DISTINCTCOUNTNOBLANK(‘Location‘[LocID] ) )
(NB. Due to Data quality a few Locations are blank at the moment so wanted to keep in the underlying data as likely to fix this but exclude them from the ranking and visuals for now (added page filter where LocID is not blank). In the main code above the – 1 is added as I found the blanks appeared as the first distinct location row in my table visual so -1 stepped everything down by 1 so that the blanks become 0 and the highest SaleAmount is ranked as 1. There may be a much better way to handle this by dealing with the blanks within the main measure perhaps?)
This approach seems to be working where I can pull the [TopPercLoc] measure into my table visualization along with the LocID column and SalesAmount column, where I change the Top % slicer to a different percentage, then the measure column in the table visual indicates the % selected if applicable or blank if it is outwith that percentage.
But I now realize I can't filter down the visual or the report page using this measure as I only want to display on the page details based on the Top % selected in the slicer (I have this incrementing up to 1 (100%) so by selecting that it would include everything if needed).
I did try something I found online which used three measures and a disconnected table to build on the measure created above to try to be able to filter by it by again using the disconnected column value in reference to the measure using a switch but couldn’t get this to work if I select only visible in the slicer I can still see all rows in the table visual whether they have a Top % value showing or are blank (outwith the Top % which I don’t want to appear) example of code for these measures below:
[Visible] = IF ( ISBLANK ( [TopPercLoc] ), BLANK() , “Visible” ) [NotVisible] = IF ( ISBLANK ( [TopPercLoc] ), “Not Visible”, BLANK() ) [MeasuresToShow] = IF ( HASONEVALUE ( [LocID] ), IF ( HASONEVALUE ( ‘Visibility‘[Visibility] ) , SWITCH ( VALUES ( ‘Visibility‘[Visibility] ), “Visible”, [Visible], “Not Visible”, [NotVisible] ), [TopPercLoc] ) )
I then created a disconnected Table using 'enter data' called Visibility with [Visibility] column and values of “Visible” and “Not Visible”.
Can anybody help with any guidance if possible please as feel I’m now going around in circles - I presume there may be much better ways to go about this. Any help would be greatly appreciated.