sorting on a joined table custom sort
Hi. I created a custom sort table and joined it to my values table so that I could sort time, on an X axis, alphabetically. For some reason, it starts out sequentially but stops after 8am. This should be relatively simply as I have a table with a custom sort, it's joined on a 1:M, but for some reason, it's not applying it. I have the custom sort field in the 'tooltips' field.
Sort data over period and count - Multi Row Card
I wish to sort and count the data as below. I have Availability data from 01-01-2023 - 01-02-2024 and wish to know the average and sort it as shown in the left MultirowCard. I also wish to know the average Availability for the period sorted by age. (Right - MultirowCard)
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
Creating a matrix sorted by date
Hello everyone, I hope you can help me with this matrix. I have the following table that I want to display in a matrix. The table is sorted (just as I would like it to appear in Power BI). This table consists of the approval process for 3 documents (IC-GP-FR-010, IC-GP-FR-011, IC-GP-IN-009). The approvers are assigned in order (Column "Responsable"). Each approver has a start date (Column "Inicio Real") which is when their turn to approve the document begins, and an end date (Column "Fin Real") which is when they finish approving. Once an approver completes their approval, the next approver in the row can proceed to approve. Flujo de Revisión Documento Responsable Inicio Real Fin Real 11000-FT-00156 IC-GP-FR-010 LUIS 10/03/2022 11/03/2022 11000-FT-00156 IC-GP-FR-010 JUAN JOSÉ 11/03/2022 6/04/2022 11000-FT-00156 IC-GP-FR-010 NATALY 6/04/2022 7/04/2022 11000-FT-00156 IC-GP-FR-010 LUIS 7/04/2022 11/04/2022 11000-FT-00156 IC-GP-FR-010 MANUEL 11/04/2022 19/04/2022 11000-FT-00156 IC-GP-FR-010 JAIME 19/04/2022 24/05/2022 11000-FT-00156 IC-GP-FR-010 MAURICIO 24/05/2022 9/06/2022 11000-FT-00156 IC-GP-FR-010 CAROLINA 9/06/2022 5/07/2022 11000-FT-00156 IC-GP-FR-010 SUSAN 5/07/2022 1/09/2022 11000-FT-00156 IC-GP-FR-010 MARITA 1/09/2022 30/09/2022 11000-FT-00156 IC-GP-FR-010 JORGE 30/09/2022 6/10/2022 11000-FT-00156 IC-GP-FR-010 JOSÉ 6/10/2022 11000-FT-00156 IC-GP-FR-011 LUIS 10/03/2022 11/03/2022 11000-FT-00156 IC-GP-FR-011 JUAN JOSÉ 11/03/2022 6/04/2022 11000-FT-00156 IC-GP-FR-011 NATALY 6/04/2022 7/04/2022 11000-FT-00156 IC-GP-FR-011 LUIS 7/04/2022 11/04/2022 11000-FT-00156 IC-GP-FR-011 MANUEL 11/04/2022 19/04/2022 11000-FT-00156 IC-GP-FR-011 JAIME 19/04/2022 24/05/2022 11000-FT-00156 IC-GP-FR-011 MAURICIO 24/05/2022 9/06/2022 11000-FT-00156 IC-GP-FR-011 CAROLINA 9/06/2022 5/07/2022 11000-FT-00156 IC-GP-FR-011 SUSAN 5/07/2022 1/09/2022 11000-FT-00156 IC-GP-FR-011 MARITA 1/09/2022 2/09/2022 11000-FT-00156 IC-GP-FR-011 JORGE 2/09/2022 7/09/2022 11000-FT-00156 IC-GP-FR-011 JOSÉ 7/09/2022 11000-FT-00156 IC-GP-IN-009 LUIS 10/03/2022 11/03/2022 11000-FT-00156 IC-GP-IN-009 JUAN JOSÉ 11/03/2022 6/04/2022 11000-FT-00156 IC-GP-IN-009 NATALY 6/04/2022 7/04/2022 11000-FT-00156 IC-GP-IN-009 LUIS 7/04/2022 11/04/2022 11000-FT-00156 IC-GP-IN-009 MANUEL 11/04/2022 19/04/2022 11000-FT-00156 IC-GP-IN-009 JAIME 19/04/2022 24/05/2022 11000-FT-00156 IC-GP-IN-009 MAURICIO 24/05/2022 23/06/2022 11000-FT-00156 IC-GP-IN-009 CAROLINA 23/06/2022 5/07/2022 11000-FT-00156 IC-GP-IN-009 SUSAN 5/07/2022 11/07/2022 11000-FT-00156 IC-GP-IN-009 MARITA 11/07/2022 30/09/2022 11000-FT-00156 IC-GP-IN-009 JORGE 30/09/2022 4/10/2022 11000-FT-00156 IC-GP-IN-009 JOSÉ 4/10/2022 3/07/2023 However, in Power BI, when I try to create the matrix, it appears disordered. I have tried to sort it by adding an index column. However, it only appears with options for showing it as a sum, average, and not without summarizing. Therefore, I cannot sort it using the index. I hope you can help me. I appreciate it in advance.
DAX measure to calculate the sum of transactions for all users equal or above a threshold percentile
Hi, I have what I thought was a fairly easy DAX problem, unfortunately I am struggling with it. I have a user input for a parameter, which takes the input of a percentile. In my data, I have client_IDs associated to any and all of my transactions. I would like to get a measure that calculates the total number of all transactions for clients with a number of transactions at or above the threshold percentile specified by the user. For example, if a user inputs 75 in the what-if parameter, the measure should produce the total number of transactions for all clients equal to or above the 25th percentile as determined by their transactions count. One caveat is that, I need to handle scenarios where there are ties. I want to settle ties arbitrarily. So for example let's say to be in the top 80 percent of clients you need to have at least 2 transactions and to be in the top 70 percent the client needs at least 3 transactions then to get the top 75 we would have to randomly choose to add some of the clients with 2 transactions to our total and to exclude the others. The ones which are kept for the total vs the ones that are not, is not important. If it were in python it would look like this: number_of_clients = dataset.groupby('client_id').size().shape[0] top_count = dataset.groupby('client_id').size().sort_values(ascending = False).cumsum()[:math.ceil(what_if_parameter*number_of_clients)].sum()
Sort matrix by measure..but 2nd level rows ignore sort
Hey everyone. I have a simple matrix that sorts by volume. I also have a time calculation group for Current and PY and the ordinal is set to the correct sort order. However...sorting the Matrix overrides the sort order of the calculation group. Is there a way to sort the matrix by the volume measure...but ignore the sort for the calculation group?Sort all values (numeric) in matrix using DAX
- Question: How to sort all values in matrix using DAX? Goal: pick N highest / lowest values with relevant dimensions. - Description: Matrix Trenddev has 2 dimensions: year (number) and location (number). The values (%) in the matrix represent the deviation between: (1) the actual value (measure, based on the actual value for that specific location and year), and (2) the calculated value based on the linear trend (measure using the actual data for the analysis period). - Analysis period (start and end year): variable thru a slicer, max 18 years. - Number of locations: max 3.000. - Goal: get the N values (N = e.g. 5 or 10) with the highest (positive) / lowest (negative) values. In other words: where has the largest deviation occurred and when? For the outcome I need the value + location and year in order to be able to do further research into the how and why. - Example: the attached Excel example is simplified for illustration and reference. Your help is highly appreciated.
Dynamic table sorting based on total count with fixed value at the bottom
Hi, I want to sort this table dynamically. So that the color category with the highest count is at the top, and the lowest at the bottom, but, I want "Unknown", to always be the last value in the table even though it has a high count. How do I do this? Thanks in advance, highly appreciate it 🙂
