Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Watch numerous video but nothiong is really showing me how to do this in Power BI.
I have a simple Matrix per week number 1, 2, 3 etc then under the week a product name. The the values of sales for those products. I want to sort the week number accending but have the products per week sorted decending by sales value. Really easy to do in Power Pivot for Excel but cannot get t to work in Power BI.
Solved! Go to 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.
Proud to be a Super User!
can anyone help still cant achieve this in Power BI?
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.
Proud to be a Super User!
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.
Proud to be a Super User!
Wow what a workaround. Thank you at least I l know I am not going mad.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
60 | |
51 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |