Forum Discussion
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".
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.
The following "Top N" (i.e. top 75) filter is applied to this matrix:
The following fields are used to generate the Matrix:
"FinancialFocus" is a field parameter with the following definitions.
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:
- Added sort column to the Field Parameter table
- Created a Measure called "FinancialFocus_RANK"
- NOTE: I believe this is where I'm failing - I don't think I'm using RANKX and ALLSELECTED properly.
- Added "FinancialFocus_RANK" to the values well of the Matrix
- The result is mostly "1's" and a couple instances of "2966".
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
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.
