cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DarylM Regular Visitor
Regular Visitor

Count Distinct Items Ranked Less than 5

Hello, 

I have a project where I need to count the number of items where the Rank is less than or equal to 5. 

Using RANKX I created a ranking for each item, but for the life of me have not been able to figure out how to count the item numbers that rank less than or equal to 5. 

 

Below in the Rank <5 field is my desired effect if it can be achived. 

 

Thanks!

2019-10-14 13_19_13-Window.png

1 ACCEPTED SOLUTION

Accepted Solutions
v-joesh-msft New Contributor
New Contributor

Re: Count Distinct Items Ranked Less than 5

Hi @DarylM ,

You can try the following measure:

item #'s less than or equal to 5 =
VAR __rank =
    ADDCOLUMNS ( 'Table', "rank", [Rankx Measure] )
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Item #] ), FILTER ( __rank, [rank] <= 5 ) )

Results are as follows:

1.PNG

Here is a demo, you can try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUUOKDmzbrdFt6VWPB...

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: Count Distinct Items Ranked Less than 5

Hi @DarylM 

If 'Rank<5'  is a calculated column you could place this measure in a card visual:

Measure =
CALCULATE ( DISTINCTCOUNT ( Table1[Item #] ), Table1[Item #] <= 5 )

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

 

DarylM Regular Visitor
Regular Visitor

Re: Count Distinct Items Ranked Less than 5

Hi Thanks!

Sorry, I realized that I should have made my comments clearer. 

The example I just created in Excel to show what I am looking for. The RANKX is a measure in Power BI and I a trying to create a measure that will count the item #'s less than or equal to 5. 

v-joesh-msft New Contributor
New Contributor

Re: Count Distinct Items Ranked Less than 5

Hi @DarylM ,

You can try the following measure:

item #'s less than or equal to 5 =
VAR __rank =
    ADDCOLUMNS ( 'Table', "rank", [Rankx Measure] )
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Item #] ), FILTER ( __rank, [rank] <= 5 ) )

Results are as follows:

1.PNG

Here is a demo, you can try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUUOKDmzbrdFt6VWPB...

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors