Percentage with slicer

[Perfecta]

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:

1. Date Slicer: Users select a date range, which filters the data.

2. Percent Slicer: Users select a range (e.g., 0–50%, 50–100%) representing the top % of SKUs, based on QTY within the selected date range.

3. SKU Ranking: SKUs are ranked by total QTY in the filtered range.
   Key Point: I want each SKU to have a unique rank (no duplicates), even if QTY is tied.

4. Dynamic Display: The report/table only shows SKUs within the selected percentile range, updating dynamically as slicers change.

   The issue:
   I’m struggling to generate unique, gapless ranks for each SKU when QTY is tied. Current DAX approaches sometimes assign the same rank to multiple SKUs, so the % filtering is inaccurate

   

   Calculated table and measures are follows:

   Table:   

   Percent Slicer = GENERATESERIES(0, 100, 1)

   Measeures:

   • Percent Range Max =MAX('Percent Slicer'[Value]) / 100
   • Percent Range Min =MIN('Percent Slicer'[Value]) / 100
   • Total QTY by SKU =SUM('Orders'[QTY])
     • SKU Rank =

                                RANKX(

                                ALLSELECTED('Orders'[SKU]),

                               CALCULATE(SUM('Orders'[QTY])) * 1000000

                             + UNICODE(LEFT(MAX('Orders'[SKU]), 1))

                            + UNICODE(MID(MAX('Orders'[SKU]), 2, 1)),

                            ,

                           DESC,

                          DENSE

                          )

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

                                                    CALCULATE(

                                                     DISTINCTCOUNT('Orders'[SKU]),

                                                    ALLSELECTED('Orders')

                                                  )

           • Show SKU =

                                                VAR ThisRank = [SKU Rank]

                                               VAR MinRank = [Min Rank]

                                              VAR MaxRank = [Max Rank]

                                            RETURN

                                               IF(

                                             ThisRank >= MinRank && ThisRank <= MaxRank,

                                                1,

                                               0

                                                ) 

             What’s the best way to achieve this dynamic percentile filtering, with unique sequential ranks for each SKU even on ties?

             Thanks in advance!

[danextian · Accepted solution]

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.

[v-agajavelly]

Hi @Perfecta ,

Just checking in one last time haven’t seen you back in a while. As per the Microsoft Fabric Community Forum guidelines, we’ll go ahead and close this thread for now. If your issue has already been resolved, that’s great to hear.

But if you still need help down or anything else feel free to create a new post anytime. The community is always here to support you.

Regards,
Akhil.

[v-agajavelly]

Hi @Perfecta ,

Just checking in were you able to give the revised SKU Rank logic a go? Totally get it if it's still in progress,  if the Unicode tiebreaker and variable split made things any smoother. Always happy to troubleshoot together if it’s still exists.

Thanks,
Akhil

[v-agajavelly]

Hi @Perfecta ,

Just circling back were you able to try out the revised SKU Rank logic with the variable split and Unicode-based tiebreaker? if it helped simplify things or if you ran into any quirks, especially with variable-length SKUs. Sometimes a small tweak like padding the SKU can make a big difference when slicing and dicing strings with LEFT, MID, and RIGHT. If you’ve got it working awesome. And if you’re still testing or need help adapting it further, happy to help in and work through it together. Looking forward to your thoughts.

Regards,
Akhil.

[v-agajavelly · Accepted solution]

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.

[v-agajavelly]

Hi @Perfecta ,

Thanks @danextian really appreciate the detailed solution.

Using a unique index to break ties in RANKX and combining it with a large multiplier is a clever and effective way to ensure unique, gapless ranks. Also, your approach with SUMMARIZECOLUMNS and dynamic filtering works perfectly for the percentile-based slicer logic.

@Perfecta  just checking in, were you able to apply this to your model? Did it help resolve the ranking and dynamic filtering as expected?

Regards,
Akhil.

[danextian · Accepted solution]

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.

[Perfecta]

Hi @danextian , Thanks for your response.

In my report I want to use a slicer for date too .Based on that the no:of sku varies and ranking also need to recalculate.Ranking is not fixed one its dynamic.I got solution right now .sharing Updated dax code 

SKU Rank =

RANKX(

    ALLSELECTED('Orders'[SKU]),

    CALCULATE(

        SUM('Orders'[QTY]) * 1000000 +

        UNICODE(LEFT(MAX('Orders'[SKU]), 1)) * 10000 +

        UNICODE(MID(MAX('Orders'[SKU]), 2, 1)) * 1000 +

        UNICODE(RIGHT(MAX('Orders'[SKU]), 3)) * 100 +

        UNICODE(RIGHT(MAX('Orders'[SKU]), 2)) * 10 +

        UNICODE(RIGHT(MAX('Orders'[SKU]), 1)) +

        RANKX(

            ALLSELECTED('Orders'[SKU]),

            MAX('Orders'[SKU]),

            ,

            ASC,

            DENSE

        ) / 1000000

    ),

    ,

    DESC,

    DENSE

)

[wardy912]

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!

[Perfecta]

Hi @wardy912  , I have updated the code as per your direction but still its showing full sku for 0-50% slicer.

[burakkaragoz]

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.