1 ACCEPTED SOLUTION
That is currently not supported. Sorting a matrix is either through a measure or row dimensions. You will need to add helper dimension from a disconnected table that returns the rank of those products by a measure.
In the screenshot below, Rank is the Value column from the disconnected Ranks table which is just a generated table of possible rank numbers.
A virtual table is created to return the quantity by color and and the rank equivalent. SUMX is then use to return the filtered value from this virtual able according to the values in Rankx[Value] column. The matrix is now to be sorted by this rank dimension
In the screenshot below, Quantity is still the same measure but rank has been replaced with a column with invisible values, and that column has been renamed to just a blank space. The +- icon has also been toggled off.
Please see the details in the sample pbix.
Hi @Si_7777
In Power BI's native matrix visual, you can sort rows by either their values or row categories. However, there is no option to sort by column categories. Instead, columns are sorted chronologically or based on custom column orders in ascending order. Values are sorted by the total column rather than individual column values, while respecting the row categories. The category with the highest total value appears first, followed by the category with the second-highest value, and so on. Dimensions in lower hierarchies are sorted either in ascending or descending order relative to the dimensions in higher hierarchies.
Hi @Si_7777
I understood that, you want to ascending the week number and products per week in descending by sales value. If you could provide the some sample data with what is your input and what is your output required in the form of any screenshots. It'll be a great to answer your questions.
Please not that, while sharing the data don't include sensitive information.
Thanks
This is what I am trying to achieve in Power BI.
I have week number 38 to 41
Under each week are products.
Then they are by decending value.
In the above I have lmited to top 5 - so I can see top 5 by value products per week.
My data model is a star schema, date > fact < products
That is currently not supported. Sorting a matrix is either through a measure or row dimensions. You will need to add helper dimension from a disconnected table that returns the rank of those products by a measure.
In the screenshot below, Rank is the Value column from the disconnected Ranks table which is just a generated table of possible rank numbers.
A virtual table is created to return the quantity by color and and the rank equivalent. SUMX is then use to return the filtered value from this virtual able according to the values in Rankx[Value] column. The matrix is now to be sorted by this rank dimension
In the screenshot below, Quantity is still the same measure but rank has been replaced with a column with invisible values, and that column has been renamed to just a blank space. The +- icon has also been toggled off.
Please see the details in the sample pbix.
