Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
jfishbac
Frequent Visitor

Auto-Filter and Auto-Sort Matrix by Selected Parameter

Hi everyone,

 

I have created a panel of 3 buttons using a Field Parameter called "FinancialFocus".

jfishbac_0-1712088272525.png

 

This button selection returns the desired values in the following Matrix. If ORDERS is chosen, then ORDERS detail for each project is shown. If REVENUE is selected, then REVENUE figures are presented. This all seems to work as expected.

jfishbac_1-1712088434142.png

 

The following "Top N" (i.e. top 75) filter is applied to this matrix:

jfishbac_4-1712088992396.png

 

The following fields are used to generate the Matrix:

jfishbac_2-1712088495339.png

 

"FinancialFocus" is a field parameter with the following definitions.

jfishbac_3-1712088549653.png

 

Where, for example, "measure_REV_USD_d" is defined as follows, which essentially returns the year-to-date Revenue Actuals, Forecast, and Budget for each Project Description in the matrix above. "measure_ORDER_USD_d" and "measure_GP_USD_d" behave similarly.

 

measure_REV_USD_d = CALCULATE(SUMX('OSGM Data_LC','OSGM Data_LC'[REV_USD_d]),AND(LEFT('OSGM Data_LC'[Period],1)="M",'OSGM Data_LC'[Period]<='OSGM Data_LC'[ctrl_ActThruPeriod]))

 

 

What I'm trying to achieve is the ability to "auto-sort" and "auto-filter" the matrix based on the button selection.

  • If ORDERS is selected, then FILTER the Matrix to show the Top 75 projects based on "Sum of ORDER_USD_d"
    • Then...SORT the Matrix in descending order base on the ORDER Values shown in the Matrix
  • If REVENUE is selected, then FILTER the Matrix to show the Top 75 projects based on "Sum of REV_USD_d"
    •  Then...SORT the Matrix in descending order base on the REVENUE Values shown in the Matrix
  • If GROSS PROFIT is selected, then FILTER the matrix to show the Top 75 projects based on "Sum of GP_USD_d"
    •  Then...SORT the Matrix in descending order base on the GROSS PROFIT Values shown in the Matrix

I attempted to use this solution from @rpinxt, but the result I get is a bunch of 1's and other odd numbers. I feel his/her solution is along the lines of what I'm looking for - so I'm sure missing something. Here is my attempt at implementing @rpinxt's solution:

 

  1.  Added sort column to the Field Parameter table
    1. jfishbac_5-1712089611405.png

       

  2. Created a Measure called "FinancialFocus_RANK"
    1. jfishbac_7-1712089816703.png
    2. NOTE: I believe this is where I'm failing - I don't think I'm using RANKX and ALLSELECTED properly.
  3. Added "FinancialFocus_RANK" to the values well of the Matrix
    1. jfishbac_8-1712089924957.png
  4. The result is mostly "1's" and a couple instances of "2966".
    1. jfishbac_9-1712090067872.png

Obviously not the result I'd want. Instead I'd like to see the first row of the matrix be a high value, and then incrementally get smaller as you scan through the matrix. Likewise, I'd like these rankings to change dynamically depending on the button selected (ORDERS, REVENUE, GROSS PROFIT), and I'd like the Top N Filter to respond in kind.

 

Hopefully someone can point me in the right direction - thanks in advance!!!

 

J

1 REPLY 1
Anonymous
Not applicable

Hi @jfishbac ,

Based on the description, try to enter the following rank measure.

 

= Rankx(all(OSGM Data_LC[ProjectDesc]), [measure],,DESC)
= Rankx(all(OSGM Data_LC[ProjectDesc]), [measure],,DESC)
= Rankx(all(OSGM Data_LC[ProjectDesc]), [measure],,DESC)

 

 

You can view the following documents to learn more about field parameters.

Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Learn

RANKX function (DAX) - DAX | Microsoft Learn

 

If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format to make a deep troubleshooting? It would be helpful to find out the solution.

You can refer the following links to share the required information.

How to provide sample data in the Power BI Forum

 

Best Regards,

Wisdom Wu

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.