Power BI Exchange

View Only

FILTER RANK INQUIRY

• 1.  FILTER RANK INQUIRY

Bronze Contributor
Posted 26 days ago
I need help displaying BOTTOM 25% in a report.  I think I need help with my column formula so if anyone has time to look at this it would be appreciated.  The Measure formula works, but I think my error is the COLUMN formula isn't properly put together throwing it all off.  I was to RANK a column, but RANK by MONTH from another column in the table so it doesn't RANK everything together.

I found something online that tells to first develop a RANK column in the table, then a MEASURE.  This actually works, but I think since I technically want to not RANK by the whole table, but RANK individually by what MONTH each row is in I have to tweak the COLUMN formula.  Any help would be appreciated.  Below is my approach.

COLUMN Formula-
BOTTOMRANKED = RANKX(TABLE1,TABLE1[PERCENT])
Is there a way to tell it to FILTER by TABLE1[MONTH] because now it is Ranking by the entire table????

MEASURE FORMULA-I think this will work if I properly rank in the COLUMN field.
BOTTOM 25 PERCENT = VAR BOTTOMFAC = COUNTROWS(ALL(TABLEW1[Facility]))*.025 RETURN Calculate(Table1[PERCENT],
FILTER(TABLE1, TABLE1[Facility]<=BOTTOMFAC))

------------------------------
Nick D
------------------------------

• 2.  RE: FILTER RANK INQUIRY

Top Contributor
Posted 25 days ago
Hi Nick:

Do you want to rank the facilities on some measure? Sum(Sales, Sales[Amount]) for example?

You can assign quartiles and percentiles to your list with these measures. This is an example for this using sales to customers.

1st ... Customer Sales 75th Percentile =

PERCENTILEX.INC (

ALL(Customers),       >> OR ALLSELECTED(Customers)

[Sales Amount],

.75

)
You do this same type of measure for 5oth percentile, 25th percentile..
Then..

Customer Quartile =

VAR FirstQuartile = [Customer Sales 25th Percentile]

VAR SecondQuartile = [Customer Sales 50th Percentile]

VAR ThirdQuartile = [Customer Sales 75th Percentile]

RETURN

IF ([Sales Amount] <= FirstQuartile, "Q1",

IF([Sales Amount] <= SecondQuartile, "Q2",

IF([Sales Amount] <= ThirdQuartile, "Q3", "Q4")

)

)

I'll attach an image what the report would look like. Not sure if this helps in your specific case but hope it does.

Thanks,

Bill S.

------------------------------
William Skelley
01106
8602807221
------------------------------

• 3.  RE: FILTER RANK INQUIRY

Bronze Contributor
Posted 25 days ago
William:
Thanks.  Let me take a stab at this and will post if it works for me a little later today.  Much appreciated.​

------------------------------
Nick D
------------------------------

• 4.  RE: FILTER RANK INQUIRY

Bronze Contributor
Posted 25 days ago
William:
Your approach was superior to what I was trying to do prior so scrapped and implemented your solution.  This works great.  Thanks so much.​

------------------------------
Nick D
------------------------------

• 5.  RE: FILTER RANK INQUIRY

Top Contributor
Posted 25 days ago
Hi Nick:

That is great and I'm happy to hear this.

I just take notes whenever I can! This forum has a lot of great ideas and solutions.

Have a good day.

Bill

------------------------------
William Skelley
01106
8602807221
------------------------------

• 6.  RE: FILTER RANK INQUIRY

Bronze Contributor
Posted 25 days ago
Yes it does.  Thanks again.

On Sep 29, 2020, at 10:21 AM, William Skelley via Power Platform User Groups <mail@connectedcommunity.org> wrote:

Power BI Exchange

Post New Message
Re: FILTER RANK INQUIRY
 Sep 29, 2020 10:19 AM William Skelley
 Hi Nick:That is great and I'm happy to hear this.I just take notes whenever I can! This forum has a lot of great ideas and solutions.Have a good day.Bill------------------------------William Skelley01106Longmeadow MA8602807221------------------------------

 You are receiving this message because you followed the 'FILTER RANK INQUIRY' message thread. To unsubscribe from this message thread, go to Unsubscribe. Update your email preferences to choose the types of email you receiveUnsubscribe from all participation emails

Original Message:
Sent: 9/29/2020 10:19:00 AM
From: William Skelley
Subject: RE: FILTER RANK INQUIRY

Hi Nick:

That is great and I'm happy to hear this.

I just take notes whenever I can! This forum has a lot of great ideas and solutions.

Have a good day.

Bill

------------------------------
William Skelley
01106
8602807221
------------------------------

Original Message:
Sent: Sep 29, 2020 09:39 AM
From: Nick D
Subject: FILTER RANK INQUIRY

William:
Your approach was superior to what I was trying to do prior so scrapped and implemented your solution.  This works great.  Thanks so much.​

------------------------------
Nick D

Original Message:
Sent: Sep 29, 2020 08:03 AM
From: Nick D
Subject: FILTER RANK INQUIRY

William:
Thanks.  Let me take a stab at this and will post if it works for me a little later today.  Much appreciated.​

------------------------------
Nick D

Original Message:
Sent: Sep 28, 2020 05:16 PM
From: William Skelley
Subject: FILTER RANK INQUIRY

Hi Nick:

Do you want to rank the facilities on some measure? Sum(Sales, Sales[Amount]) for example?

You can assign quartiles and percentiles to your list with these measures. This is an example for this using sales to customers.

1st ... Customer Sales 75th Percentile =

PERCENTILEX.INC (

ALL(Customers),       >> OR ALLSELECTED(Customers)

[Sales Amount],

.75

)
You do this same type of measure for 5oth percentile, 25th percentile..
Then..

Customer Quartile =

VAR FirstQuartile = [Customer Sales 25th Percentile]

VAR SecondQuartile = [Customer Sales 50th Percentile]

VAR ThirdQuartile = [Customer Sales 75th Percentile]

RETURN

IF ([Sales Amount] <= FirstQuartile, "Q1",

IF([Sales Amount] <= SecondQuartile, "Q2",

IF([Sales Amount] <= ThirdQuartile, "Q3", "Q4")

)

)

I'll attach an image what the report would look like. Not sure if this helps in your specific case but hope it does.

Thanks,

Bill S.

------------------------------
William Skelley
01106
8602807221

Original Message:
Sent: Sep 28, 2020 10:44 AM
From: Nick D
Subject: FILTER RANK INQUIRY

I need help displaying BOTTOM 25% in a report.  I think I need help with my column formula so if anyone has time to look at this it would be appreciated.  The Measure formula works, but I think my error is the COLUMN formula isn't properly put together throwing it all off.  I was to RANK a column, but RANK by MONTH from another column in the table so it doesn't RANK everything together.

I found something online that tells to first develop a RANK column in the table, then a MEASURE.  This actually works, but I think since I technically want to not RANK by the whole table, but RANK individually by what MONTH each row is in I have to tweak the COLUMN formula.  Any help would be appreciated.  Below is my approach.

COLUMN Formula-
BOTTOMRANKED = RANKX(TABLE1,TABLE1[PERCENT])
Is there a way to tell it to FILTER by TABLE1[MONTH] because now it is Ranking by the entire table????

MEASURE FORMULA-I think this will work if I properly rank in the COLUMN field.
BOTTOM 25 PERCENT = VAR BOTTOMFAC = COUNTROWS(ALL(TABLEW1[Facility]))*.025 RETURN Calculate(Table1[PERCENT],
FILTER(TABLE1, TABLE1[Facility]<=BOTTOMFAC))

------------------------------
Nick D
------------------------------