Forum Discussion
Percentage with slicer
Hi Perfecta
You can add an index or rank column in the query editor and incorporate either of these columns when ranking column categories.
Category Rank (No Tie) = RANKX ( ALLSELECTED ( 'Table'[Category] ), CALCULATE ( ( SUM ( 'Table'[Score] ) * 10000000 ) + SUM ( 'Table'[Rank] ) ), , DESC, DENSE )Category Rank % = DIVIDE ( [Category Rank (No Tie)], COUNTROWS ( ALLSELECTED ( 'Table'[Category] ) ) )You can evalute Category Rank % over a table or table expression which is your case is ALLSELECTED(Orders[SKU]) and filter it depending on the min and max values in the numeric/percentage parameter.
Filtered Category Rank % = MAXX ( FILTER ( SUMMARIZECOLUMNS ( 'Table'[Category], "@rank %", [Category Rank %] ), [@rank %] >= MIN ( Percentage[Percentage] ) && [@rank %] <= MAX ( Percentage[Percentage] ) ), [@rank %] )You can use a similar measure above to return the total value (which is score in my example) within the percentage range selected in a card which doesn't have a row context.
Score within Filtered Category Rank % = SUMX ( FILTER ( SUMMARIZECOLUMNS ( 'Table'[Category], "@rank %", [Category Rank %], "@score", CALCULATE ( SUM ( 'Table'[Score] ) ) ), [@rank %] >= MIN ( Percentage[Percentage] ) && [@rank %] <= MAX ( Percentage[Percentage] ) ), [@score] )Please see the attached pbix for the details.
Hi Perfecta ,
If your SKU always follows a fixed-length format (e.g., always 6 characters), this logic works perfectly. But if SKU lengths vary, you might want to pad the string or normalize it first to avoid misalignment in the LEFT, MID, RIGHT operations. Also, for clarity and maintainability, consider splitting this into variables.SKU Rank =
VAR BaseQty = SUM('Orders'[QTY])
VAR SKUValue = MAX('Orders'[SKU])
VAR TieBreaker = UNICODE(LEFT(SKUValue, 1)) * 10000 +
UNICODE(MID(SKUValue, 2, 1)) * 1000 +
UNICODE(RIGHT(SKUValue, 3)) * 100 +
UNICODE(RIGHT(SKUValue, 2)) * 10 +
UNICODE(RIGHT(SKUValue, 1)) +
RANKX(ALLSELECTED('Orders'[SKU]),SKUValue,,ASC,DENSE ) / 1000000
RETURN
RANKX(ALLSELECTED('Orders'[SKU]),BaseQty * 1000000 + TieBreaker,,DESC,DENSE)Same logic, but much easier to debug and maintain.
Regards,
Akhil.
Hi Perfecta
Instead of relying on RANKX alone, you can use a combination of QTY and a unique identifier (like SKU) to break ties and generate a gapless, unique rank
SKU Unique Rank =
VAR SummaryTable =
ADDCOLUMNS(
SUMMARIZE('Orders', 'Orders'[SKU]),
"TotalQTY", CALCULATE(SUM('Orders'[QTY]))
)
VAR SortedTable =
ADDCOLUMNS(
SummaryTable,
"RankIndex",
RANKX(
SummaryTable,
[TotalQTY] * 1000000 + UNICODE(LEFT([SKU], 1)) * 1000 + UNICODE(MID([SKU], 2, 1)),
,
DESC,
SKIP
)
)
RETURN
MAXX(
FILTER(SortedTable, [SKU] = MAX('Orders'[SKU])),
[RankIndex]
)
Then, calculate total SKUs in range
Total SKUs in Range =
CALCULATE(
DISTINCTCOUNT('Orders'[SKU]),
ALLSELECTED('Orders')
)
Next, calculate percentile rank
SKU Rank Percent =
DIVIDE([SKU Unique Rank], [Total SKUs in Range])
Use MIN/MAX slicer values
Min Rank = [Percent Range Min] * [Total SKUs in Range]
Max Rank = [Percent Range Max] * [Total SKUs in Range]
Filter SKUs based on percentile
Show SKU =
VAR ThisRank = [SKU Unique Rank]
VAR MinRank = [Min Rank]
VAR MaxRank = [Max Rank]
RETURN
IF(ThisRank >= MinRank && ThisRank <= MaxRank, 1, 0)
Then, add the visual level filter 'show sku = 1'
If this helps please give a thumbs up and mark as solved, thanks!
