Need help with showing data for Sellers ranked between 1 to 10 (dynamic user selection)

[Rashmi_Veena]

Hello All,

I need to show the Rank of Sellers by their count in selected year (Count YTD). Give the ability to users to filter sellers for rank 1 to 10 or 10-20 (whatever they desire). This works fine using RANK measure and then creating a numeric range parameter for slicer selection.

The problem comes, when I try to show the data for only these sellers which has ranks selected by the user in another table with Price Range and Count YTD. 

I have shared the measures and expected output. Kindly help.

Measure1

Rank Sellers

    RANKX(ALL(Sample_Data[Seller]),

         [Count YTD],, DESC)

Measure2 (to filter the sellers based on user selection)

Selected Rank Sellers = 

VAR _MaxSel = MAX('Top N Sellers'[Top N Sellers])

VAR _MinSel = Min('Top N Sellers'[Top N Sellers])

RETURN

INT(AND ([Rank Sellers] <= _MaxSel , [Rank Sellers] >= _MinSel ))

If user selectes 1 to 10 in slicer, I get the following output- 

Seller	Count YTD
RVJNISH	79193
MINAR	56796
GROUPS	25760
GVM	20716
MHOMES	13247
MORRISON	13167
DHOMES	12321
KHOME	11666
LHOMES	10279
Rajeev	9651
Grand Total	252796

The requirement is to show -

Closings Price Range	Count YTD
$250k-$499k	1,47,721
$100k-$249k	60,660
$500k-$999k	31,028
<$100k	10,952
$1M or More	2,435

[v-venuppu]

Hi @Rashmi_Veena ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-venuppu]

Hi @Rashmi_Veena ,

Apologies for the late response. 

I have implemented the logic using a smaller dataset and attached the .pbix file here. The measure "Count YTD Selected Sellers" correctly filters the Top N sellers based on rank and shows the count per Price Range.

• The values now vary by Price Range, which confirms the filtering works as intended.
  

1.Created Parameter Table (Top N Sellers)

2.A slicer is used to control the Top N Sellers (from a separate parameter table).

3.Created four DAX Measures (1.Count YTD_Measure 2.Rank Sellers 3.Selected Rank Sellers 4.Count YTD Selected Sellers) - Able to find them in pbix file.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[v-venuppu]

Hi @Rashmi_Veena ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

Regards,

Rama U.

[v-venuppu]

Hi @Rashmi_Veena ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Regards,

Rama U.

[v-venuppu]

Hi @Rashmi_Veena ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Regards,

Rama U.

[Rashmi_Veena]

Hi , apologies for the dealy in response. 

[bhanu_gautam · Accepted solution]

@Rashmi_Veena Create a measure to rank the sellers.

Rank Sellers =
RANKX(
ALL(Sample_Data[Seller]),
[Count YTD],
,
DESC
)

Selected Rank Sellers Measure:

Selected Rank Sellers =
VAR _MaxSel = MAX('Top N Sellers'[Top N Sellers])
VAR _MinSel = MIN('Top N Sellers'[Top N Sellers])
RETURN
INT(AND([Rank Sellers] <= _MaxSel, [Rank Sellers] >= _MinSel))

Count YTD for Selected Sellers Measure:

DAX
Count YTD Selected Sellers =
CALCULATE(
[Count YTD],
FILTER(
Sample_Data,
[Selected Rank Sellers] = 1
)
)

Count YTD by Price Range Measure:

DAX
Count YTD by Price Range =
SWITCH(
TRUE(),
Sample_Data[Price Range] = "$250k-$499k", CALCULATE([Count YTD Selected Sellers], Sample_Data[Price Range] = "$250k-$499k"),
Sample_Data[Price Range] = "$100k-$249k", CALCULATE([Count YTD Selected Sellers], Sample_Data[Price Range] = "$100k-$249k"),
Sample_Data[Price Range] = "$500k-$999k", CALCULATE([Count YTD Selected Sellers], Sample_Data[Price Range] = "$500k-$999k"),
Sample_Data[Price Range] = "<$100k", CALCULATE([Count YTD Selected Sellers], Sample_Data[Price Range] = "<$100k"),
Sample_Data[Price Range] = "$1M or More", CALCULATE([Count YTD Selected Sellers], Sample_Data[Price Range] = "$1M or More")
)

[Rashmi_Veena]

Thank you for such detailed response. I created all your measures as suggestes. Had to tweak Count YTD by Price Range (had to use Selectedvalue(price range) in olace of TRUE()). Then I placed Price Range and this measure in a table and it is throwing "Visual has exceeded the availble resources" . My dataset has around 4 million rows.

[v-venuppu]

Hi @Rashmi_Veena ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @bhanu_gautam for the prompt response.

The "Visual has exceeded the available resources" error in Power BI usually happens due to inefficient row-level filtering or heavy context evaluation-especially with large datasets (~4M rows, as in your case).

Here's few modifications that help to resolve the issue:

1. Rank Sellers

Add this measure:

Rank Sellers =
RANKX(ALL(Sample_Data[Seller]), [Count YTD], , DESC)

2. User-Selected Rank Filter

 Create this:

Selected Rank Sellers =
VAR _MaxSel = MAX('Top N Sellers'[Top N Sellers])
VAR _MinSel = MIN('Top N Sellers'[Top N Sellers])
RETURN
IF([Rank Sellers] >= _MinSel && [Rank Sellers] <= _MaxSel, 1, 0)

3. Count YTD for Selected Sellers

This gives you total count for sellers in selected rank range:

Count YTD Selected Sellers =
CALCULATE(
[Count YTD],
FILTER(ALL(Sample_Data), [Selected Rank Sellers] = 1)
)

4. Show by Price Range

Put Price Range in a table and use this measure:

Count YTD by Price Range =
CALCULATE(
[Count YTD Selected Sellers]
)

If performance still lags:

• Add [Selected Rank Sellers] = 1 as a visual-level filter on your table.
• Use your original [Count YTD] measure-much faster.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 Thank you.

 

[Rashmi_Veena]

Can you share the PBIX where these logic are working? And, thank you so much for all your efforts and time. 

[Rashmi_Veena]

Thanks for your response. I treid the logic on a smaller dataset (862 rows). When I add Price Range and Count YTD by price range in a table, I get same number for all the price ranges.