Back in January 2016, I wrote a blog post explaining a DAX workaround that allows you to put measures on rows in a matrix in a Power BI report. I’m happy to say that you no longer need my workaround because you can now natively put measures on rows in a matrix in both Power BI Desktop and PowerBI.com.
This is accomplished via a new formatting option for the matrix.
As a quick example, I made a table with years on columns and measures in my values (and nothing on rows). I added three measures: Sales Amount, Total Cost, Gross Margin.
Initially, my matrix puts the measures across the columns.
But I can change that in the formatting options.
Find the Values section on the formatting pane and look for Show On rows. Toggle that switch to On.
And that gives you the three values on rows.
great and helpful blog to everyone.. thanks a lot for sharing
Thank you Meagan!
I don’t see options for Rows, Columns, and Values on my Power BI desktop.
I see options for “Page level filters”, “Report level filters”, “Values”.
How can I see the options for “Rows” and “Columns”?
Hi, Michael. You must add the matrix to the page and then have the matrix selected to see the rows, values, and columns. They will be where you see values. If you are only seeing values, you have most likely selected a table instead of a matrix.
Yes, I figured it out late Thursday night. But Meagan thank you so much for replying to my question; you have no idea how much I appreciate it.
Thanks Meagan! But how would you use this new feature to create the table as in your original workaround? Here, each column is a Year, which is pretty straightforward. In the original, you had different, overlapping time-frames (current week/month/year). It feels like the original post still applies in that situation?
Thanks, Meagan! But how would you use this new feature to create the table as in your original workaround? Here, each column is a Year, which is pretty straightforward. In the original, you had different, overlapping time-frames (current week/month/year). It feels like the original post might still apply in that situation?
Thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thanks Meagan, I’ve looked for this solution many times, and it’s been there all the time, right in front of me. Great stuff!
Fantastic – thank you for sharing this suggestion. I’ve been searching for ages online to try and find somewhere that explains how to do this.
Thank You
Thank you very much indeed!
When I export it doesn’t retain this formatting. Any ideas on that?
Did you export with current layout? https://powerbi.microsoft.com/en-cy/blog/export-to-excel-improvements-for-table-and-matrix-visuals-preview/
Is it possible to have another column that calculates the Year on Year variance between the measures?
Yes, you can use DAX to create a YOY variance measure and add it to your matrix.
Hey, I spent 8 hours researching about this. I had tried everything in powerBI.
Thanks!!!!!
Thanks, it really help me.