Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

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
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: FILTER RANK INQUIRY

    Top Contributor
    Posted 25 days ago
      |   view attached
    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
    Longmeadow MA
    8602807221
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 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
    Longmeadow MA
    8602807221
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 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:
    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...
    Power Platform User Groups

    Power BI Exchange

    Post New Message
    Academy - Online Interactive Learning from Experts
    Re: FILTER RANK INQUIRY
    Reply to Group Reply to Sender
    William Skelley
    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 Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------
      Reply to Group Online   View Thread   Recommend   Forward   Flag as Inappropriate  




     
    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 receive

    Unsubscribe 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
    Longmeadow MA
    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
    Longmeadow MA
    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
    ------------------------------
    Academy - Online Interactive Learning from Experts