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.