Forum Discussion

Perfecta's avatar
Perfecta
Frequent Visitor
1 year ago
Solved

Percentage with slicer

Hi all, I have an Orders table in Power BI with columns: SKU, Sales Date, QTY, etc. I want to build a report with these requirements: Date Slicer: Users select a date range, which filters the da...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.



burakkaragoz's avatar
burakkaragoz
Super User
1 year ago

Hi Perfecta ,

 

You're on the right track, and I totally get the challenge — handling ties in ranking while maintaining a clean percentile filter can be tricky in Power BI.

The issue with RANKX is that even with DENSE ranking, ties will still result in duplicate ranks, which throws off your percentile logic. To get unique, gapless ranks, even when QTYs are tied, you can introduce a tiebreaker that ensures uniqueness.

Here’s a refined approach:

1. Use a composite key for ranking

Instead of just using QTY, combine it with a unique identifier (like SKU text) to break ties:

SKU Rank =
RANKX(
    ALLSELECTED('Orders'[SKU]),
    CALCULATE(
        SUM('Orders'[QTY]) * 1000000 +
        UNICODE(LEFT(MAX('Orders'[SKU]), 1)) * 1000 +
        UNICODE(MID(MAX('Orders'[SKU]), 2, 1))
    ),
    ,
    ASC,
    DENSE
)

This ensures that even if QTY is the same, the SKU string will break the tie deterministically.

2. Calculate total SKUs

Total SKUs in Range =
CALCULATE(
    DISTINCTCOUNT('Orders'[SKU]),
    ALLSELECTED('Orders')
)

3. Convert rank to percentile

SKU Rank Percent =
DIVIDE([SKU Rank], [Total SKUs in Range])

4. Filter based on slicer

Make sure your slicer table (Percent Slicer) is disconnected and use this logic:

Show SKU =
VAR ThisPercent = [SKU Rank Percent]
VAR MinPercent = [Percent Range Min]
VAR MaxPercent = [Percent Range Max]
RETURN
IF(
    ThisPercent >= MinPercent && ThisPercent <= MaxPercent,
    1,
    0
)

Then use Show SKU = 1 as a visual-level filter.

This setup should give you a dynamic, percentile-based filtering experience with unique ranks, even when QTYs are tied.

Let me know if you want help turning this into a reusable pattern or if you're still seeing edge cases!

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.